using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.OracleClient;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using Common.TextMatch;

namespace Common
{
    /// <summary>
    /// 
    /// </summary>
    public partial class DbUtil
    {
        #region Schema -- database
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static DataTable GetServerSchema(string connStr)
        {
            DataTable dtServerSchema = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                dtServerSchema = conn.GetSchema("Databases");
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtServerSchema;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static DataTable GetDatabaseSchema(string connStr)
        {
            DataTable dtDatabaseSchema = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                string db = DbConnUtil.GetDatabaseName(connStr);
                dtDatabaseSchema = conn.GetSchema("Databases", new string[] { db });
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtDatabaseSchema;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static DatabaseFileGroup[] GetDatabaseFileGroups(string connStr)
        {
            List<DatabaseFileGroup> dbFiles = new List<DatabaseFileGroup>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select type_desc, [name], physical_name, [size] from sys.database_files";
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DatabaseFileGroup fg = new DatabaseFileGroup();
                    fg.Name = reader["name"].ToString();
                    fg.Type = reader["type_desc"].ToString();
                    fg.Size = long.Parse(reader["size"].ToString());
                    fg.FilePath = reader["physical_name"].ToString();
                    dbFiles.Add(fg);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dbFiles.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static DataTable GetMetaDataCollection(string connStr)
        {
            DataTable dtMetaData = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                dtMetaData = conn.GetSchema("MetadataCollections");
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtMetaData;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static string[] GetDatabases(string connStr)
        {
            List<string> dbNames = new List<string>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select [name] from sys.databases";
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    dbNames.Add(reader["name"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            if (dbNames.Count > 0)
            {
                dbNames.Sort();
                return dbNames.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static List<TableSynonym> GetSynonyms(string connStr)
        {
            List<TableSynonym> synonyms=new List<TableSynonym>();
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = @"select 
[Name] as SynonymName,
base_object_name as SynonymRef 
from sys.synonyms";
                SqlDataReader reader = cmd.ExecuteReader();
                Regex partsRegex = new Regex(@"^\[?([^\[\]\.]+)\]?\.\[?([^\[\]\.]+)\]?\.\[?([^\[\]\.]+)\]?$");
                while(reader.Read())
                {
                    string synonymName = reader["SynonymName"].ToString();
                    string synonymRef = reader["SynonymRef"].ToString();
                    Match partsMatch = partsRegex.Match(synonymRef);
                    if(partsMatch.Success)
                    {
                        string dbName = partsMatch.Groups[1].Value;
                        string schemaName = partsMatch.Groups[2].Value;
                        string tableName = partsMatch.Groups[3].Value;
                        TableSynonym synonym = new TableSynonym(synonymName, dbName, schemaName, tableName);
                        synonyms.Add(synonym);    
                    }
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return synonyms;
        }
        #endregion

        #region change schema
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <param name="colDbType"></param>
        public static void ChangeColumnDbType(string connStr, string tblName, string colName, FieldDataType colDbType)
        {
            string sp_ChangeColDbType = @"
create procedure ChangeColumnDbType 
(
@tblName nvarchar(255),
@colName nvarchar(255),
@dbType nvarchar(255)
) as 
begin 
	begin try
		begin tran 
		declare @renameSql nvarchar(2000)
		set @renameSql='exec sp_rename '''+@tblName +'.'+@colName+''', ''temp_oldColName'', ''Column'''
		print 'rename sql: '+@renameSql 
		exec sp_executesql @renameSql 

		declare @alterSql nvarchar(2000)
		set @alterSql='ALTER TABLE '+@tblName +' Add ' +@colName +' '+@dbType
		print 'Alter sql: '+@alterSql 
		exec sp_executesql @alterSql 

		declare @updateSql nvarchar(2000)
		set @updateSql ='UPDATE '+@tblName +' set '+@colName+'=Cast(temp_OldColName as '+@dbType+')'
		print 'Update sql: '+@updateSql
		exec sp_executesql @updateSql 

		set @alterSql='ALTER TABLE '+@tblName +' drop column temp_oldColName'
		print 'Alter sql: '+@alterSql 
		exec sp_executesql @alterSql 
		commit tran
	end try
	begin catch
		print 'Error number='+Cast(ERROR_Number() as varchar) +', Error state='+cast(Error_State() as varchar)
		print 'Line '+cast(Error_Line() as varchar) +':'+Error_Message()
		rollback tran 
	end catch 
end ";
            string spName = "ChangeColumnDbType";
            
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string dropSpSql = StoredProcUtil.CreateDropStoredProcedureStatement(DataProviderType.MSSQL, spName);
                cmd.CommandText = dropSpSql;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                cmd.CommandText = sp_ChangeColDbType;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter tblParam=new SqlParameter("@tblName",tblName);
                SqlParameter colParam = new SqlParameter("@colName",colName);
                SqlParameter dbTypeParam=new SqlParameter("@dbType",FieldDataType.ToMssqlFieldDataTypeString(colDbType));
                cmd.Parameters.AddRange(new SqlParameter[]{tblParam,colParam,dbTypeParam});
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region tables and views
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static DatabaseTableDescription[] GetTableDescriptions(string connStr)
        {
            List<DatabaseTableDescription> dbTables = new List<DatabaseTableDescription>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select [name], create_date, modify_date from sys.all_objects where type='u'";
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    DatabaseTableDescription dbTbl = new DatabaseTableDescription();
                    dbTbl.Name = reader["name"].ToString();
                    dbTbl.CreationDate = DateTime.Parse(reader["create_date"].ToString());
                    dbTbl.ModificationDate = DateTime.Parse(reader["modify_date"].ToString());
                    dbTbl.FullTextIndexed = false;
                    dbTbl.HaveKeys = false;
                    dbTbl.MaxRowSize = 0;
                    dbTbl.RecordCount = 0;
                    dbTbl.Schema = string.Empty;
                    dbTbl.ReservedSize = "";
                    dbTbl.DataSize = "";
                    dbTbl.IndexSize = "";
                    dbTbl.UnUsedSize = "";
                    dbTables.Add(dbTbl);
                }
                reader.Close();

                if (dbTables.Count > 0)
                {
                    for (int i = 0; i < dbTables.Count; i++)
                    {
                        // row size
                        TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, string.Empty, dbTables[i].Name);
                        bool maintainKey = false;
                        long rowSize = 0;
                        if (cols != null && cols.Length > 0)
                        {
                            for (int k = 0; k < cols.Length; k++)
                            {
                                if (cols[k].IsPK)
                                {
                                    maintainKey = true;
                                }
                                rowSize += cols[k].GetMaxFieldSize();
                            }
                        }
                        DatabaseTableDescription dbTblDesc = dbTables[i];
                        dbTblDesc.MaxRowSize = rowSize;
                        dbTblDesc.HaveKeys = maintainKey;
                        bool ftIndexed = DbUtil.IsTableFullTextIndexed(connStr, dbTables[i].Name);
                        dbTblDesc.FullTextIndexed = ftIndexed;
                        // record size
                        dbTblDesc.RecordCount = DbUtil.GetTableRecordCount(connStr, dbTables[i].Name);
                        // file size 
                        cmd.CommandText = "sp_spaceused";
                        cmd.CommandType = CommandType.StoredProcedure;
                        SqlParameter tblNameParam = new SqlParameter("@objname", dbTblDesc.Name);
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add(tblNameParam);
                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            dbTblDesc.ReservedSize = reader["Reserved"].ToString();
                            dbTblDesc.DataSize = reader["Data"].ToString();
                            dbTblDesc.IndexSize = reader["Index_Size"].ToString();
                            dbTblDesc.UnUsedSize = reader["Unused"].ToString();
                        }
                        reader.Close();
                        dbTables[i] = dbTblDesc;
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dbTables.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static DateTime GetTableModificationTime(string connStr, string tblName)
        {
            DateTime modificationTime = DateTime.Now;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
                    "select modify_date from sys.all_objects where type='u' and [name]='" +
                    tblName + "'";
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    modificationTime = DateTime.Parse(reader["modify_date"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return modificationTime;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static DateTime GetTableModificationTime(DataProviderType provider, string connStr, string schemaName, string tblName)
        {
            if (provider == DataProviderType.MSSQL)
                return DbUtil.GetTableModificationTime(connStr, tblName);
            else
            {
                DateTime dbModificationTime = DateTime.Now;
                return dbModificationTime;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static int GetTableRecordCount(DataProviderType provider, string connStr, string schemaName, string tblName)
        {
            int count = 0;
            if(provider== DataProviderType.Oracle)
            {
                OracleConnection oraConn = new OracleConnection(connStr);
                try
                {
                    oraConn.Open();
                    OracleCommand oraCmd = oraConn.CreateCommand();
                    ((IDbCommand)oraCmd).CommandTimeout = 60*15;

                    oraCmd.CommandText = "select count (*) from " +
                                         (string.IsNullOrEmpty(schemaName) ? tblName : schemaName + "." + tblName);
                    OracleDataReader oraReader = oraCmd.ExecuteReader();
                    if(oraReader.Read())
                    {
                        try
                        {
                            count = int.Parse(oraReader[0].ToString());
                        }
                        catch
                        {
                            count = 0;
                        }
                    }
                }
                catch(Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    oraConn.Close();
                }
            }
            else if(provider== DataProviderType.MSSQL)
            {
                SqlConnection sqlConn = new SqlConnection(connStr);
                try
                {
                    sqlConn.Open();
                    SqlCommand cmd = sqlConn.CreateCommand();
                    cmd.CommandTimeout = 5 * 60;
                    cmd.CommandText = "select count (*) from " +
                                      ((string.IsNullOrEmpty(schemaName) ? "[dbo]" : "[" + schemaName + "]")
                                       + ".[" + tblName + "]");
                    SqlDataReader reader = cmd.ExecuteReader();
                    if(reader.Read())
                    {
                        try
                        {
                            count = int.Parse(reader[0].ToString());
                        }
                        catch
                        {
                            count = 0;
                        }
                    }
                    reader.Close();
                }
                catch(Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    sqlConn.Close();
                }
            }
            else
            {
                throw new NotSupportedException("Data provider not supported");
            }
            return count;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="selectSql"></param>
        /// <returns></returns>
        public static int GetTableRecordCount(DataProviderType provider, 
            string connStr, string selectSql)
        {
            int count = 0;
            string selectCountSql = string.Format("select count (*) from ({0}) tmp", selectSql);
            if (provider == DataProviderType.Oracle)
            {
                OracleConnection oraConn = new OracleConnection(connStr);
                try
                {
                    oraConn.Open();
                    OracleCommand oraCmd = oraConn.CreateCommand();
                    ((IDbCommand)oraCmd).CommandTimeout = 60 * 15;

                    oraCmd.CommandText = selectCountSql;
                    OracleDataReader oraReader = oraCmd.ExecuteReader();
                    if (oraReader.Read())
                    {
                        try
                        {
                            count = int.Parse(oraReader[0].ToString());
                        }
                        catch
                        {
                            count = 0;
                        }
                    }
                    oraReader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    oraConn.Close();
                }
            }
            else if (provider == DataProviderType.MSSQL)
            {
                SqlConnection sqlConn = new SqlConnection(connStr);
                try
                {
                    sqlConn.Open();
                    SqlCommand cmd = sqlConn.CreateCommand();
                    cmd.CommandTimeout = 5 * 60;
                    cmd.CommandText = selectCountSql;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        try
                        {
                            count = int.Parse(reader[0].ToString());
                        }
                        catch
                        {
                            count = 0;
                        }
                    }
                    reader.Close();

                    if (count==0)
                    {
                        string sql = selectSql;
                        if(sql.ToLower().IndexOf("order by ")>0)
                        {
                            sql = sql.Substring(0, sql.ToLower().LastIndexOf("order by"));
                        }
                        sql = string.Format("select count (*) from ({0}) temp", sql);
                        cmd.CommandText = sql;
                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            try
                            {
                                count = int.Parse(reader[0].ToString());
                            }
                            catch
                            {
                                count = 0;
                            }
                        }
                        reader.Close();
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    sqlConn.Close();
                }
            }
            else
            {
                throw new NotSupportedException("Data provider not supported");
            }
            return count;
        }

		/// <summary>
		/// 
		/// </summary>
		/// <param name="dataProviderType"></param>
		/// <param name="connStr"></param>
		/// <param name="spName"></param>
		/// <returns></returns>
		public static int GetTableRecordCountUsingSP(DataProviderType dataProviderType, string connStr, string spName)
		{
			int count = 0;
			if (dataProviderType == DataProviderType.Oracle)
			{
				OracleConnection oraConn = new OracleConnection(connStr);
				try
				{
					oraConn.Open();
					OracleCommand oraCmd = oraConn.CreateCommand();
					((IDbCommand)oraCmd).CommandTimeout = 60 * 15;
					oraCmd.CommandType = CommandType.StoredProcedure;
					oraCmd.CommandText = spName;
					OracleDataReader oraReader = oraCmd.ExecuteReader();
					count = oraReader.RecordsAffected;
					oraReader.Close();
				}
				catch (Exception ex)
				{
					ExceptionHandler.HandlesException(ex);
				}
				finally
				{
					oraConn.Close();
				}
			}
			else if (dataProviderType == DataProviderType.MSSQL)
			{
				SqlConnection sqlConn = new SqlConnection(connStr);
				try
				{
					sqlConn.Open();
					SqlCommand cmd = sqlConn.CreateCommand();
					cmd.CommandTimeout = 5 * 60;
					cmd.CommandText = spName;
					cmd.CommandType = CommandType.StoredProcedure;
					SqlDataReader reader = cmd.ExecuteReader();
					count = reader.RecordsAffected;
					reader.Close();
				}
				catch (Exception ex)
				{
					ExceptionHandler.HandlesException(ex);
				}
				finally
				{
					sqlConn.Close();
				}
			}
			else
			{
				throw new NotSupportedException("Data provider not supported");
			}
			return count;
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="selectSql"></param>
        /// <param name="timeOut"></param>
        /// <returns></returns>
        public static int GetTableRecordCount(DataProviderType provider, string connStr, string selectSql, int timeOut)
        {
            int count = 0;
            int toPos = selectSql.ToLower().IndexOf("from") + "from".Length;
            string selectCountSql = "select count (*) from " + selectSql.Substring(toPos);
            if (provider == DataProviderType.Oracle)
            {
                OracleConnection oraConn = new OracleConnection(connStr);
                try
                {
                    oraConn.Open();
                    OracleCommand oraCmd = oraConn.CreateCommand();
                    ((IDbCommand)oraCmd).CommandTimeout = timeOut;

                    oraCmd.CommandText = selectCountSql;
                    OracleDataReader oraReader = oraCmd.ExecuteReader();
                    if (oraReader.Read())
                    {
                        try
                        {
                            count = int.Parse(oraReader[0].ToString());
                        }
                        catch
                        {
                            count = 0;
                        }
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    oraConn.Close();
                }
            }
            else if (provider == DataProviderType.MSSQL)
            {
                SqlConnection sqlConn = new SqlConnection(connStr);
                try
                {
                    sqlConn.Open();
                    SqlCommand cmd = sqlConn.CreateCommand();
                    cmd.CommandTimeout = timeOut;
                    cmd.CommandText = selectCountSql;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        try
                        {
                            count = int.Parse(reader[0].ToString());
                        }
                        catch
                        {
                            count = 0;
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    sqlConn.Close();
                }
            }
            else
            {
                throw new NotSupportedException("Data provider not supported");
            }
            return count;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static DatabaseTableDescription GetTableDescription(string connStr, string tblName)
        {
            DatabaseTableDescription dbTbl = new DatabaseTableDescription();

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select [name], create_date, modify_date from sys.all_objects where type='u' and [name]='" + tblName + "'";
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    dbTbl.Name = reader["name"].ToString();
                    dbTbl.CreationDate = DateTime.Parse(reader["create_date"].ToString());
                    dbTbl.ModificationDate = DateTime.Parse(reader["modify_date"].ToString());
                    dbTbl.FullTextIndexed = false;
                    dbTbl.HaveKeys = false;
                    dbTbl.MaxRowSize = 0;
                    dbTbl.RecordCount = 0;
                    dbTbl.Schema = string.Empty;
                }
                reader.Close();


                // row size
                TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, string.Empty, dbTbl.Name);
                bool maintainKey = false;
                long rowSize = 0;
                if (cols != null && cols.Length > 0)
                {
                    for (int k = 0; k < cols.Length; k++)
                    {
                        if (cols[k].IsPK)
                        {
                            maintainKey = true;
                        }
                        rowSize += cols[k].GetMaxFieldSize();
                    }
                }

                dbTbl.MaxRowSize = rowSize;
                dbTbl.HaveKeys = maintainKey;
                bool ftIndexed = DbUtil.IsTableFullTextIndexed(connStr, dbTbl.Name);
                dbTbl.FullTextIndexed = ftIndexed;
                // record size
                dbTbl.RecordCount = DbUtil.GetTableRecordCount(connStr, dbTbl.Name);
                // file size 
                cmd.CommandText = "sp_spaceused";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter tblNameParam = new SqlParameter("@objname", dbTbl.Name);
                cmd.Parameters.Clear();
                cmd.Parameters.Add(tblNameParam);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    dbTbl.ReservedSize = reader["Reserved"].ToString();
                    dbTbl.DataSize = reader["Data"].ToString();
                    dbTbl.IndexSize = reader["Index_Size"].ToString();
                    dbTbl.UnUsedSize = reader["Unused"].ToString();
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return dbTbl;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static DatabaseTableDescription GetTableDescription(DataProviderType provider, string connStr, string schemaName, string tblName)
        {
            if (provider == DataProviderType.MSSQL)
                return DbUtil.GetTableDescription(connStr, tblName);
            else
            {
                DatabaseTableDescription dbTbl = new DatabaseTableDescription();
                SqlConnection conn = new SqlConnection(connStr);
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText =
                        "select [name], create_date, modify_date from sys.all_objects where type='u' and [name]='" +
                        tblName + "'";
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        dbTbl.Name = reader["name"].ToString();
                        dbTbl.CreationDate = DateTime.Parse(reader["create_date"].ToString());
                        dbTbl.ModificationDate = DateTime.Parse(reader["modify_date"].ToString());
                        dbTbl.FullTextIndexed = false;
                        dbTbl.HaveKeys = false;
                        dbTbl.MaxRowSize = 0;
                        dbTbl.RecordCount = 0;
                        dbTbl.Schema = string.Empty;
                    }
                    reader.Close();


                    // row size
                    TableColumn[] cols =
                        DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, string.Empty, dbTbl.Name);
                    bool maintainKey = false;
                    long rowSize = 0;
                    if (cols != null && cols.Length > 0)
                    {
                        for (int k = 0; k < cols.Length; k++)
                        {
                            if (cols[k].IsPK)
                            {
                                maintainKey = true;
                            }
                            rowSize += cols[k].GetMaxFieldSize();
                        }
                    }

                    dbTbl.MaxRowSize = rowSize;
                    dbTbl.HaveKeys = maintainKey;
                    bool ftIndexed = DbUtil.IsTableFullTextIndexed(connStr, dbTbl.Name);
                    dbTbl.FullTextIndexed = ftIndexed;
                    // record size
                    dbTbl.RecordCount = DbUtil.GetTableRecordCount(connStr, dbTbl.Name);
                    // file size 
                    cmd.CommandText = "sp_spaceused";
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter tblNameParam = new SqlParameter("@objname", dbTbl.Name);
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(tblNameParam);
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        dbTbl.ReservedSize = reader["Reserved"].ToString();
                        dbTbl.DataSize = reader["Data"].ToString();
                        dbTbl.IndexSize = reader["Index_Size"].ToString();
                        dbTbl.UnUsedSize = reader["Unused"].ToString();
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }

                return dbTbl;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static SimpleTableDescription[] GetSimpleTableDescriptions(string connStr)
        {
            List<SimpleTableDescription> simpleTblDescs = new List<SimpleTableDescription>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select name, create_date from sys.objects where type='u'";
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    SimpleTableDescription tblDesc = new SimpleTableDescription();
                    tblDesc.TableName = reader["name"].ToString();
                    tblDesc.CreationTime = DateTime.Parse(reader["create_date"].ToString());
                    tblDesc.FieldCount = 0;
                    tblDesc.RowSize = 0;
                    simpleTblDescs.Add(tblDesc);
                }
                reader.Close();

                if (simpleTblDescs.Count > 0)
                {
                    for (int i = 0; i < simpleTblDescs.Count; i++)
                    {
                        SimpleTableDescription tbl = simpleTblDescs[i];
                        cmd.CommandText = "select count (*) from [" + tbl.TableName + "]";
                        try
                        {
                            tbl.RowSize = int.Parse(cmd.ExecuteScalar().ToString());
                        }
                        catch
                        {
                            tbl.RowSize = 0;
                        }
                        cmd.CommandText = "select count (*) from sys.columns where object_id=object_id('" +
                            tbl.TableName + "')";
                        try
                        {
                            tbl.FieldCount = int.Parse(cmd.ExecuteScalar().ToString());
                        }
                        catch
                        {
                            tbl.FieldCount = 0;
                        }
                        simpleTblDescs[i] = tbl;
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return simpleTblDescs.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static string[] GetTables(string connStr)
        {
            List<string> tblNames = new List<string>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                DataTable dtSchema = conn.GetSchema("Tables");
                if (dtSchema.Rows.Count > 0)
                {
                    for (int i = 0; i < dtSchema.Rows.Count; i++)
                    {
                        tblNames.Add(dtSchema.Rows[i][2].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            if (tblNames.Count > 0)
            {
                tblNames.Sort();
                return tblNames.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static string[] GetSchemaNames(DataProviderType dbProvider, string connStr)
        {
            List<string> schemaNames=new List<string>();
            if (dbProvider == DataProviderType.Oracle)
            {
                OracleConnection conn = new OracleConnection(connStr);
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select USERNAME from SYS.ALL_USERS order by USERNAME";
                    OracleDataReader reader = cmd.ExecuteReader();
                    while(reader.Read())
                    {
                        schemaNames.Add(reader.GetString(0));
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                SqlConnection conn = new SqlConnection(connStr);
                try
                {
                    conn.Open();
                    DataTable dtSchema = conn.GetSchema("Tables");
                    if (dtSchema != null && dtSchema.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dtSchema.Rows)
                        {
                            string schema = dr[1].ToString();
                            if (!schemaNames.Contains(schema))
                            {
                                schemaNames.Add(schema);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            schemaNames.Sort();
            return schemaNames.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="schemaName"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static string[] GetTableNames(DataProviderType dbProvider, string schemaName, string connStr)
        {
            if (dbProvider == DataProviderType.MSSQL)
            {
                if (string.IsNullOrEmpty(schemaName))
                    schemaName = "dbo";
                List<string> tblNames = new List<string>();
                SqlConnection conn = new SqlConnection(connStr);
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    string sql = @"
select 
Table_Schema as SchemaName,
Table_Name as TableName 
from INFORMATION_SCHEMA.TABLES 
where Table_Schema='{0}'";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format(sql, schemaName);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while(reader.Read())
                    {
                        string tblName = reader["TableName"].ToString();
                        tblNames.Add(tblName);
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
                return tblNames.ToArray();
            }
            else if(dbProvider==DataProviderType.Oracle)
            {
                List<string> tblNames=new List<string>();
                OracleConnection conn = new OracleConnection(connStr);
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select OWNER, TABLE_NAME from SYS.ALL_TABLES order by OWNER, TABLE_NAME";
                    OracleDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        string schema = dr[0].ToString();
                        string tblName = dr[1].ToString();
                        // string tableType = dr[2].ToString();
                        if (string.IsNullOrEmpty(schemaName))
                        {
                            tblNames.Add(tblName);
                        }
                        else if (schema.ToLower() == schemaName.ToLower())
                        {
                            tblNames.Add(tblName);
                        }
                    }
                    dr.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }

                return tblNames.ToArray();
            }
            else 
                throw new Exception("Data provider not support in schema discovery");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static Dictionary<string,List<string>> GetTableNamesWithSchemas(
            DataProviderType dbProvider, string connStr)
        {
            Dictionary<string,List<string>> tblNamesBySchema=new Dictionary<string, List<string>>();
            string[] schemaNames = DbUtil.GetSchemaNames(dbProvider, connStr);
            if(schemaNames !=null && schemaNames.Length>0)
            {
                foreach(string schemaName in schemaNames)
                {
                    string[] tblNames = DbUtil.GetTableNames(dbProvider, schemaName, connStr);
                    if(tblNames !=null && tblNames.Length>0)
                    {
                        List<string> tblNameList=new List<string>();
                        tblNameList.AddRange(tblNames);
                        tblNamesBySchema.Add(schemaName, tblNameList);
                    }
                }
            }
            return tblNamesBySchema;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="schemaName"></param>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static string[] GetViewNames(DataProviderType dbProvider, string schemaName, string connStr)
        {
            if (dbProvider == DataProviderType.MSSQL)
                return DbUtil.GetViews(connStr);
            else if (dbProvider == DataProviderType.Oracle)
            {
                List<string> tblNames = new List<string>();
                OracleConnection conn = new OracleConnection(connStr);
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select OWNER, VIEW_NAME from SYS.ALL_VIEWS order by OWNER, VIEW_NAME";
                    OracleDataReader reader = cmd.ExecuteReader();
                    while(reader.Read())
                    {
                        string schema = reader[0].ToString();
                        string tblName = reader[1].ToString();
                        // string tableType = dr[2].ToString();
                        if (string.IsNullOrEmpty(schemaName))
                        {
                            tblNames.Add(tblName);
                        }
                        else if (schema.ToLower() == schemaName.ToLower())
                        {
                            tblNames.Add(tblName);
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }

                return tblNames.ToArray();
            }
            else
                throw new Exception("Data provider not support in schema discovery");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static string[] GetViews(string connStr)
        {
            List<string> vwNames = new List<string>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                DataTable dtSchema = conn.GetSchema("Views");
                if (dtSchema != null && dtSchema.Rows.Count > 0)
                {
                    for (int i = 0; i < dtSchema.Rows.Count; i++)
                    {
                        vwNames.Add(dtSchema.Rows[i][2].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            if (vwNames.Count > 0)
            {
                vwNames.Sort();
                return vwNames.ToArray();
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region stored procs
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static StoredProcedure[] GetStoredProcs(string connStr)
        {
            List<StoredProcedure> sps = new List<StoredProcedure>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select name, crdate from sys.sysobjects where type='p' order by name";
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    StoredProcedure sp = new StoredProcedure();
                    sp.Name = reader["name"].ToString();
                    sp.Parameters = new SqlParam[] { };
                    sp.Schema = string.Empty;
                    sp.SQL = string.Empty;
                    sp.CreateTime = DateTime.Parse(reader["crdate"].ToString());
                    sps.Add(sp);
                }
                reader.Close();

                if (sps.Count > 0)
                {
                    for (int i = 0; i < sps.Count; i++)
                    {
                        StoredProcedure sp = sps[i];
                        List<SqlParam> paramList = new List<SqlParam>();

                        cmd.CommandText = "select " +
                            "[name], type_name(user_type_id) as Type " +
                            " from sys.all_parameters where object_id = object_id('" +
                            sp.Name + "')";
                        reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            SqlParam param = new SqlParam();
                            param.Name = reader["name"].ToString();
                            param.Direction = 0;
                            param.Type = reader["type"].ToString();
                            paramList.Add(param);
                        }
                        reader.Close();
                        sp.Parameters = paramList.ToArray();

                        cmd.CommandText = "sp_helptext '" + sp.Name + "'";
                        reader = cmd.ExecuteReader();
                        System.Text.StringBuilder buffer = new StringBuilder();
                        while (reader.Read())
                        {
                            buffer.Append(reader.GetString(reader.GetOrdinal("Text")));

                        }
                        reader.Close();
                        sp.SQL = buffer.ToString();

                        sps[i] = sp;
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            
            return sps.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="spName"></param>
        /// <returns></returns>
        public static StoredProcedure GetStoredProcedure(string connStr, string spName)
        {
            StoredProcedure sp = new StoredProcedure();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select name, crdate " +
                    "from sys.sysobjects where type='p' and name='" +
                    spName + "'";
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    sp.Name = reader["name"].ToString();
                    sp.Parameters = new SqlParam[] { };
                    sp.Schema = string.Empty;
                    sp.SQL = string.Empty;
                    sp.CreateTime = DateTime.Parse(reader["crdate"].ToString());
                }
                reader.Close();

                List<SqlParam> paramList = new List<SqlParam>();

                cmd.CommandText = "select " +
                    "[name], type_name(user_type_id) as Type " +
                    " from sys.all_parameters where object_id = object_id('" +
                    sp.Name + "')";
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    SqlParam param = new SqlParam();
                    param.Name = reader["name"].ToString();
                    param.Direction = 0;
                    param.Type = reader["type"].ToString();
                    paramList.Add(param);
                }
                reader.Close();
                sp.Parameters = paramList.ToArray();

                cmd.CommandText = "sp_helptext '" + sp.Name + "'";
                reader = cmd.ExecuteReader();
                System.Text.StringBuilder buffer = new StringBuilder();
                while (reader.Read())
                {
                    buffer.Append(reader.GetString(reader.GetOrdinal("Text")));
                }
                reader.Close();
                sp.SQL = buffer.ToString();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return sp;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="spName"></param>
        /// <returns></returns>
        public static bool IsStoredProcedureExist(string connStr, string spName)
        {
            bool exist = false;
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string sql =
                    @"SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')";
                cmd.CommandText = string.Format(sql, spName);
                SqlDataReader reader = cmd.ExecuteReader();
                if(reader.Read())
                {
                    exist = true;
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return exist;
        }
        #endregion

        #region check
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static bool IsTableExist(DataProviderType dbProvider, string connStr, string tblName)
        {
            if (dbProvider == DataProviderType.Oracle)
                return DbUtil.IsOracleTableExist(connStr, tblName);

            string[] tblNames = DbUtil.GetTables(connStr);
            bool exist = false;
            if (tblNames != null && tblNames.Length > 0)
            {
                foreach (string name in tblNames)
                {
                    if (name.ToLower() == tblName.ToLower())
                    {
                        exist = true;
                        break;
                    }
                }
            }
            return exist;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static bool IsOracleTableExist(string connStr, string tblName)
        {
            Dictionary<string,List<string>> schemaTables = DbUtil.GetTableNamesWithSchemas(DataProviderType.Oracle, connStr);
            foreach(string schemaName in schemaTables.Keys)
            {
                foreach(string tableName in schemaTables[schemaName])
                {
                    if(tblName.ToLower()==tableName.ToLower())
                        return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="colNames"></param>
        /// <returns></returns>
        public static bool IsTableColumnExist(string connStr, string tblName, List<string> colNames)
        {
            TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, "", tblName);
            int validColCount = 0;
            for (int i = 0; i < colNames.Count; i++)
            {
                colNames[i] = colNames[i].ToLower();
            }
            foreach (TableColumn col in cols)
            {
                if (colNames.Contains(col.ColumnName.ToLower()))
                {
                    validColCount += 1;
                }
            }
            if (validColCount == colNames.Count)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="minRecordCount"></param>
        /// <returns></returns>
        public static bool IsTableRecordCountCorrect(string connStr, string tblName, int minRecordCount)
        {
            bool correct = false;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select count (*) from " + tblName;
                try
                {
                    int count = int.Parse(cmd.ExecuteScalar().ToString());
                    if (count >= minRecordCount)
                    {
                        correct = true;
                    }
                }
                catch
                {
                    correct = false;
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return correct;
        }
        #endregion

        #region columns
        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static string[] GetTableColumnNames(DataProviderType provider, string connStr, string schemaName, string tblName)
        {
            List<string> colNames = new List<string>();
            switch (provider)
            {
                case DataProviderType.MSSQL:
                    SqlConnection conn1 = new SqlConnection(connStr);
                    try
                    {
                        conn1.Open();
                        SqlCommand cmd1 = conn1.CreateCommand();
                        cmd1.CommandTimeout = 60;    // set timeout to 1 min
                        cmd1.CommandText = "select top 1 * from " +
                                           (string.IsNullOrEmpty(schemaName) ? string.Empty : schemaName + ".") +
                                           "[" + tblName + "]";
                        SqlDataReader reader1 = cmd1.ExecuteReader();
                        DataTable dtSchema1 = reader1.GetSchemaTable();
                        reader1.Close();
                        if (dtSchema1 != null && dtSchema1.Rows.Count > 0)
                        {
                            for (int i = 0; i < dtSchema1.Rows.Count; i++)
                            {
                                colNames.Add(dtSchema1.Rows[i]["ColumnName"].ToString());
                            }
                        }
                    }
                    catch(Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        conn1.Close();
                    }
                    break;
                case DataProviderType.Oracle:
                    OracleConnection conn2 = new OracleConnection(connStr);
                    try
                    {
                        conn2.Open();
                        OracleCommand cmd2 = conn2.CreateCommand();
                        ((IDbCommand)cmd2).CommandTimeout = 60;    // set timeout to 1 min
                        cmd2.CommandText = "select * from " +
                            (string.IsNullOrEmpty(schemaName) ? tblName : schemaName + "." + tblName) +
                            " where rownum=1";
                        OracleDataReader reader2 = cmd2.ExecuteReader();
                        DataTable dtSchema2 = reader2.GetSchemaTable();
                        reader2.Close();
                        if (dtSchema2 != null && dtSchema2.Rows.Count > 0)
                        {
                            for (int i = 0; i < dtSchema2.Rows.Count; i++)
                            {
                                colNames.Add(dtSchema2.Rows[i]["ColumnName"].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                       ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        conn2.Close();
                    }
                    break;
            }

            return colNames.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static TableColumn[] GetTableColumns(DataProviderType provider, string connStr, string schemaName, string tblName)
        {
            List<TableColumn> cols = new List<TableColumn>();
            switch (provider)
            {
                case DataProviderType.MSSQL:
                    SqlConnection conn1 = new SqlConnection(connStr);
                    try
                    {
                        conn1.Open();
                        SqlCommand cmd1 = conn1.CreateCommand();
                        cmd1.CommandTimeout = 60;    // set timeout to 1 min
                        cmd1.CommandText = "select top 1 * from " +
                                           (string.IsNullOrEmpty(schemaName) ? "dbo." : "[" + schemaName + "].") +
                                           "[" + tblName + "]";
                        SqlDataReader reader1 = cmd1.ExecuteReader();
                        DataTable dtSchema1 = reader1.GetSchemaTable();
                        reader1.Close();
                        if (dtSchema1 != null && dtSchema1.Rows.Count > 0)
                        {
                            for (int i = 0; i < dtSchema1.Rows.Count; i++)
                            {
                                TableColumn col = new TableColumn();
                                col.ColumnName = dtSchema1.Rows[i]["ColumnName"].ToString();
                                col.Ordinal = int.Parse(dtSchema1.Rows[i]["ColumnOrdinal"].ToString());
                                col.Size = 0;
                                try
                                {
                                    col.Size = int.Parse(dtSchema1.Rows[i]["ColumnSize"].ToString());
                                }
                                catch
                                {
                                    col.Size = 0;
                                }
                                col.Precision = 0;
                                try
                                {
                                    col.Precision = int.Parse(dtSchema1.Rows[i]["NumericPrecision"].ToString());
                                }
                                catch
                                {
                                    col.Precision = 0;
                                }
                                col.Scale = 0;
                                try
                                {
                                    col.Scale = int.Parse(dtSchema1.Rows[i]["NumericScale"].ToString());
                                }
                                catch
                                {
                                    col.Scale = 0;
                                }
                                col.AllowNull = (bool)dtSchema1.Rows[i]["AllowDBNull"];
                                col.IsIdentity = (bool)dtSchema1.Rows[i]["IsIdentity"];
                                string dbTypeStr = dtSchema1.Rows[i]["DataType"].ToString();
                                if (dbTypeStr == typeof(string).ToString() && col.Size > 8000)
                                {
                                    col.DbType = SqlDbType.Text;
                                }
                                else
                                {
                                    col.DbType = FieldDataType.ToDbType(dbTypeStr);
                                }
                                try
                                {
                                    bool isKey = (bool)dtSchema1.Rows[i]["IsKey"];
                                    bool isUnique = (bool)dtSchema1.Rows[i]["IsUnique"];
                                    if (isKey && isUnique)
                                    {
                                        col.IsPK = true;
                                    }
                                    else
                                    {
                                        col.IsPK = false;
                                    }
                                }
                                catch
                                {
                                    col.IsPK = false;
                                }
                                cols.Add(col);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        conn1.Close();
                    }
                    break;
                case DataProviderType.Oracle:
                    OracleConnection conn2 = new OracleConnection(connStr);
                    try
                    {
                        conn2.Open();
                        OracleCommand cmd2 = conn2.CreateCommand();
                        ((IDbCommand)cmd2).CommandTimeout = 60;    // set timeout to 1 min
                        cmd2.CommandText = "select * from " +
                            (string.IsNullOrEmpty(schemaName) ? tblName : schemaName + "." + tblName) + 
                            " where rownum=1";
                        OracleDataReader reader2 = cmd2.ExecuteReader();
                        DataTable dtSchema2 = reader2.GetSchemaTable();
                        reader2.Close();
                        if (dtSchema2 != null && dtSchema2.Rows.Count > 0)
                        {
                            for (int i = 0; i < dtSchema2.Rows.Count; i++)
                            {
                                TableColumn col = new TableColumn();
                                col.ColumnName = dtSchema2.Rows[i]["ColumnName"].ToString();
                                col.Ordinal = int.Parse(dtSchema2.Rows[i]["ColumnOrdinal"].ToString());
                                col.Size = 0;
                                try
                                {
                                    col.Size = int.Parse(dtSchema2.Rows[i]["ColumnSize"].ToString());
                                }
                                catch
                                {
                                    col.Size = 0;
                                }
                                col.Precision = 0;
                                try
                                {
                                    col.Precision = int.Parse(dtSchema2.Rows[i]["NumericPrecision"].ToString());
                                }
                                catch
                                {
                                    col.Precision = 0;
                                }
                                col.Scale = 0;
                                try
                                {
                                    col.Scale = int.Parse(dtSchema2.Rows[i]["NumericScale"].ToString());
                                }
                                catch
                                {
                                    col.Scale = 0;
                                }
                                col.AllowNull = (bool)dtSchema2.Rows[i]["AllowDBNull"];
                                col.IsIdentity = false;
                                string dbTypeStr = dtSchema2.Rows[i]["DataType"].ToString();
                                col.DbType = FieldDataType.ToDbType(dbTypeStr);
                                try
                                {
                                    bool isKey = (bool)dtSchema2.Rows[i]["IsKey"];
                                    bool isUnique = (bool)dtSchema2.Rows[i]["IsUnique"];
                                    if (isKey && isUnique)
                                    {
                                        col.IsPK = true;
                                    }
                                    else
                                    {
                                        col.IsPK = false;
                                    }
                                }
                                catch
                                {
                                    col.IsPK = false;
                                }
                                cols.Add(col);
                            }
                        }
                    }
                    catch(Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        conn2.Close();
                    }
                    break;
            }

            if (cols.Count > 0)
            {
                if (provider == DataProviderType.Oracle)
                    return cols.ToArray();
                else
                {
                    List<string> pkFieldNames = DbUtil.GetTablePrimaryKeys(connStr, "", tblName);
                    if (pkFieldNames != null && pkFieldNames.Count > 0)
                    {
                        Hashtable htPks = new Hashtable();
                        for (int i = 0; i < pkFieldNames.Count; i++)
                        {
                            htPks.Add(pkFieldNames[i].ToLower(), pkFieldNames[i]);
                        }

                        for (int i = 0; i < cols.Count; i++)
                        {
                            if (htPks.Contains(cols[i].ColumnName.ToLower()))
                            {
                                TableColumn pkCol = cols[i];
                                pkCol.IsPK = true;
                                cols[i] = pkCol;
                            }
                        }
                    }

                    for (int i = 0; i < cols.Count; i++)
                    {
                        TableColumn col = cols[i];
                        col.IsFK = DbUtil.IsTableColumnForeignKey(connStr, tblName, col.ColumnName);
                        col.IsIndexed = DbUtil.IsTableColumnIndexed(connStr, tblName, col.ColumnName);
                        cols[i] = col;
                    }
                    return cols.ToArray();
                }
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static List<TableColumn> GetTableColumns2(DataProviderType provider, string connStr, string schemaName, string tblName)
        {
            List<TableColumn> cols = new List<TableColumn>();
            switch (provider)
            {
                case DataProviderType.MSSQL:
                    SqlConnection conn1 = new SqlConnection(connStr);
                    try
                    {
                        conn1.Open();
                        SqlCommand cmd1 = conn1.CreateCommand();
                        cmd1.CommandTimeout = 60;    // set timeout to 1 min
                        cmd1.CommandText = "select top 1 * from " +
                                           (string.IsNullOrEmpty(schemaName) ? string.Empty : schemaName + ".") +
                                           "[" + tblName + "]";
                        SqlDataReader reader1 = cmd1.ExecuteReader();
                        DataTable dtSchema1 = reader1.GetSchemaTable();
                        reader1.Close();
                        if (dtSchema1 != null && dtSchema1.Rows.Count > 0)
                        {
                            for (int i = 0; i < dtSchema1.Rows.Count; i++)
                            {
                                TableColumn col = new TableColumn();
                                col.ColumnName = dtSchema1.Rows[i]["ColumnName"].ToString();
                                col.Ordinal = int.Parse(dtSchema1.Rows[i]["ColumnOrdinal"].ToString());
                                col.Size = 0;
                                try
                                {
                                    col.Size = int.Parse(dtSchema1.Rows[i]["ColumnSize"].ToString());
                                }
                                catch
                                {
                                    col.Size = 0;
                                }
                                col.Precision = 0;
                                try
                                {
                                    col.Precision = int.Parse(dtSchema1.Rows[i]["NumericPrecision"].ToString());
                                }
                                catch
                                {
                                    col.Precision = 0;
                                }
                                col.Scale = 0;
                                try
                                {
                                    col.Scale = int.Parse(dtSchema1.Rows[i]["NumericScale"].ToString());
                                }
                                catch
                                {
                                    col.Scale = 0;
                                }
                                col.AllowNull = (bool)dtSchema1.Rows[i]["AllowDBNull"];
                                col.IsIdentity = (bool)dtSchema1.Rows[i]["IsIdentity"];
                                string dbTypeStr = dtSchema1.Rows[i]["DataType"].ToString();
                                if (dbTypeStr == typeof(string).ToString() && col.Size > 8000)
                                {
                                    col.DbType = SqlDbType.Text;
                                }
                                else
                                {
                                    col.DbType = FieldDataType.ToDbType(dbTypeStr);
                                }
                                try
                                {
                                    bool isKey = (bool)dtSchema1.Rows[i]["IsKey"];
                                    bool isUnique = (bool)dtSchema1.Rows[i]["IsUnique"];
                                    if (isKey && isUnique)
                                    {
                                        col.IsPK = true;
                                    }
                                    else
                                    {
                                        col.IsPK = false;
                                    }
                                }
                                catch
                                {
                                    col.IsPK = false;
                                }
                                cols.Add(col);
                            }
                        }
                    }
                    catch(Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        conn1.Close();
                    }
                    break;
                case DataProviderType.Oracle:
                    OracleConnection conn2 = new OracleConnection(connStr);
                    try
                    {
                        conn2.Open();
                        OracleCommand cmd2 = conn2.CreateCommand();
                        ((IDbCommand)cmd2).CommandTimeout = 60;    // set timeout to 1 min
                        cmd2.CommandText = "select * from " +
                            (string.IsNullOrEmpty(schemaName) ? tblName : schemaName + "." + tblName) + 
                            " where rownum=1";
                        OracleDataReader reader2 = cmd2.ExecuteReader();
                        DataTable dtSchema2 = reader2.GetSchemaTable();
                        reader2.Close();
                        if (dtSchema2 != null && dtSchema2.Rows.Count > 0)
                        {
                            for (int i = 0; i < dtSchema2.Rows.Count; i++)
                            {
                                TableColumn col = new TableColumn();
                                col.ColumnName = dtSchema2.Rows[i]["ColumnName"].ToString();
                                col.Ordinal = int.Parse(dtSchema2.Rows[i]["ColumnOrdinal"].ToString());
                                col.Size = 0;
                                try
                                {
                                    col.Size = int.Parse(dtSchema2.Rows[i]["ColumnSize"].ToString());
                                }
                                catch
                                {
                                    col.Size = 0;
                                }
                                col.Precision = 0;
                                try
                                {
                                    col.Precision = int.Parse(dtSchema2.Rows[i]["NumericPrecision"].ToString());
                                }
                                catch
                                {
                                    col.Precision = 0;
                                }
                                col.Scale = 0;
                                try
                                {
                                    col.Scale = int.Parse(dtSchema2.Rows[i]["NumericScale"].ToString());
                                }
                                catch
                                {
                                    col.Scale = 0;
                                }
                                col.AllowNull = (bool)dtSchema2.Rows[i]["AllowDBNull"];
                                col.IsIdentity = false;
                                string dbTypeStr = dtSchema2.Rows[i]["DataType"].ToString();
                                col.DbType = FieldDataType.ToDbType(dbTypeStr);
                                try
                                {
                                    bool isKey = (bool)dtSchema2.Rows[i]["IsKey"];
                                    bool isUnique = (bool)dtSchema2.Rows[i]["IsUnique"];
                                    if (isKey && isUnique)
                                    {
                                        col.IsPK = true;
                                    }
                                    else
                                    {
                                        col.IsPK = false;
                                    }
                                }
                                catch
                                {
                                    col.IsPK = false;
                                }
                                cols.Add(col);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        conn2.Close();
                    }
                    break;
            }

            if (cols.Count > 0)
            {
                if (provider == DataProviderType.Oracle)
                    return cols;
                else
                {
                    List<string> pkFieldNames = DbUtil.GetTablePrimaryKeys(connStr, "", tblName);
                    if (pkFieldNames != null && pkFieldNames.Count > 0)
                    {
                        Hashtable htPks = new Hashtable();
                        for (int i = 0; i < pkFieldNames.Count; i++)
                        {
                            htPks.Add(pkFieldNames[i].ToLower(), pkFieldNames[i]);
                        }

                        for (int i = 0; i < cols.Count; i++)
                        {
                            if (htPks.Contains(cols[i].ColumnName.ToLower()))
                            {
                                TableColumn pkCol = cols[i];
                                pkCol.IsPK = true;
                                cols[i] = pkCol;
                            }
                        }
                    }


                    for (int i = 0; i < cols.Count; i++)
                    {
                        TableColumn col = cols[i];
                        col.IsFK = DbUtil.IsTableColumnForeignKey(connStr, tblName, col.ColumnName);
                        col.IsIndexed = DbUtil.IsTableColumnIndexed(connStr, tblName, col.ColumnName);
                        cols[i] = col;
                    }
                    return cols;
                }
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="selectStatement"></param>
        /// <returns></returns>
        public static TableColumn[] GetTableColumns(string connStr, string selectStatement)
        {
            List<TableColumn> cols = new List<TableColumn>();
            SqlConnection conn1 = new SqlConnection(connStr);
            try
            {
                conn1.Open();
                SqlCommand cmd1 = conn1.CreateCommand();
                cmd1.CommandTimeout = 60;    // set timeout to 1 min
                cmd1.CommandText = selectStatement;
                SqlDataReader reader1 = cmd1.ExecuteReader();
                DataTable dtSchema1 = reader1.GetSchemaTable();
                reader1.Close();
                if (dtSchema1 != null && dtSchema1.Rows.Count > 0)
                {
                    for (int i = 0; i < dtSchema1.Rows.Count; i++)
                    {
                        TableColumn col = new TableColumn();
                        col.ColumnName = dtSchema1.Rows[i]["ColumnName"].ToString();
                        col.Ordinal = int.Parse(dtSchema1.Rows[i]["ColumnOrdinal"].ToString());
                        col.Size = 0;
                        try
                        {
                            col.Size = int.Parse(dtSchema1.Rows[i]["ColumnSize"].ToString());
                        }
                        catch
                        {
                            col.Size = 0;
                        }
                        col.Precision = 0;
                        try
                        {
                            col.Precision = int.Parse(dtSchema1.Rows[i]["NumericPrecision"].ToString());
                        }
                        catch
                        {
                            col.Precision = 0;
                        }
                        col.Scale = 0;
                        try
                        {
                            col.Scale = int.Parse(dtSchema1.Rows[i]["NumericScale"].ToString());
                        }
                        catch
                        {
                            col.Scale = 0;
                        }
                        col.AllowNull = (bool)dtSchema1.Rows[i]["AllowDBNull"];
                        col.IsIdentity = (bool)dtSchema1.Rows[i]["IsIdentity"];
                        string dbTypeStr = dtSchema1.Rows[i]["DataType"].ToString();
                        if (dbTypeStr == typeof(string).ToString() && col.Size > 8000)
                        {
                            col.DbType = SqlDbType.Text;
                        }
                        else
                        {
                            col.DbType = FieldDataType.ToDbType(dbTypeStr);
                        }
                        try
                        {
                            bool isKey = (bool)dtSchema1.Rows[i]["IsKey"];
                            bool isUnique = (bool)dtSchema1.Rows[i]["IsUnique"];
                            if (isKey && isUnique)
                            {
                                col.IsPK = true;
                            }
                            else
                            {
                                col.IsPK = false;
                            }
                        }
                        catch
                        {
                            col.IsPK = false;
                        }
                        col.IsFK = false;
                        col.IsIndexed = false;
                        cols.Add(col);
                    }
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn1.Close();
            }

            if (cols.Count > 0)
            {
                return cols.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="selectSql"></param>
        /// <returns></returns>
        public static TableColumn[] GetTableColumns(DataProviderType provider, string connStr, string selectSql)
        {
            List<TableColumn> cols = new List<TableColumn>();
            switch (provider)
            {
                case DataProviderType.MSSQL:
                    SqlConnection conn1 = new SqlConnection(connStr);
                    try
                    {
                        conn1.Open();
                        SqlCommand cmd1 = conn1.CreateCommand();
                        cmd1.CommandTimeout = 60;    // set timeout to 1 min
                        Regex distinctRegex=new Regex("select distinct",RegexOptions.IgnoreCase);
                        Match distinctMatch = distinctRegex.Match(selectSql);
                        if(distinctMatch.Success)
                        {
                            selectSql = distinctRegex.Replace(selectSql, "select ");
                        }
                        
                        if (selectSql.ToLower().IndexOf("top") < 0)
                        {
                            int selectPos = selectSql.ToLower().IndexOf("select");
                            if (selectPos >= 0)
                            {
                                selectSql = selectSql.Substring(0, selectPos) + " select top 1 " +
                                            selectSql.Substring(selectPos + "select".Length);
                            }
                        }
                        cmd1.CommandText = selectSql;
                        SqlDataReader reader1 = cmd1.ExecuteReader();
                        DataTable dtSchema1 = reader1.GetSchemaTable();
                        reader1.Close();
                        if (dtSchema1 != null && dtSchema1.Rows.Count > 0)
                        {
                            for (int i = 0; i < dtSchema1.Rows.Count; i++)
                            {
                                TableColumn col = new TableColumn();
                                col.ColumnName = dtSchema1.Rows[i]["ColumnName"].ToString();
                                col.Ordinal = int.Parse(dtSchema1.Rows[i]["ColumnOrdinal"].ToString());
                                col.Size = 0;
                                try
                                {
                                    col.Size = int.Parse(dtSchema1.Rows[i]["ColumnSize"].ToString());
                                }
                                catch
                                {
                                    col.Size = 0;
                                }
                                col.Precision = 0;
                                try
                                {
                                    col.Precision = int.Parse(dtSchema1.Rows[i]["NumericPrecision"].ToString());
                                }
                                catch
                                {
                                    col.Precision = 0;
                                }
                                col.Scale = 0;
                                try
                                {
                                    col.Scale = int.Parse(dtSchema1.Rows[i]["NumericScale"].ToString());
                                }
                                catch
                                {
                                    col.Scale = 0;
                                }
                                col.AllowNull = (bool)dtSchema1.Rows[i]["AllowDBNull"];
                                col.IsIdentity = (bool)dtSchema1.Rows[i]["IsIdentity"];
                                string dbTypeStr = dtSchema1.Rows[i]["DataType"].ToString();
                                if (dbTypeStr == typeof(string).ToString() && col.Size > 8000)
                                {
                                    col.DbType = SqlDbType.Text;
                                }
                                else
                                {
                                    col.DbType = FieldDataType.ToDbType(dbTypeStr);
                                }
                                try
                                {
                                    bool isKey = (bool)dtSchema1.Rows[i]["IsKey"];
                                    bool isUnique = (bool)dtSchema1.Rows[i]["IsUnique"];
                                    if (isKey && isUnique)
                                    {
                                        col.IsPK = true;
                                    }
                                    else
                                    {
                                        col.IsPK = false;
                                    }
                                }
                                catch
                                {
                                    col.IsPK = false;
                                }
                                cols.Add(col);
                            }
                        }
                    }
                    catch(Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        conn1.Close();
                    }
                    break;
                case DataProviderType.Oracle:
                    OracleConnection conn2 = new OracleConnection(connStr);
                    try
                    {
                        conn2.Open();
                        OracleCommand cmd2 = conn2.CreateCommand();
                        ((IDbCommand)cmd2).CommandTimeout = 600;    // set timeout to 10 min
                        if (selectSql.ToLower().IndexOf("where") > 0)
                        {
                            int wherePos = selectSql.ToLower().LastIndexOf("where") + "where".Length;
                            cmd2.CommandText = selectSql.Substring(0, wherePos) + " rownum=1 and " + selectSql.Substring(wherePos);
                        }
                        else
                        {
                            cmd2.CommandText = selectSql +
                              " where rownum=1";
                        }
                       
                        OracleDataReader reader2 = cmd2.ExecuteReader();
                        DataTable dtSchema2 = reader2.GetSchemaTable();
                        reader2.Close();
                        if (dtSchema2 != null && dtSchema2.Rows.Count > 0)
                        {
                            for (int i = 0; i < dtSchema2.Rows.Count; i++)
                            {
                                TableColumn col = new TableColumn();
                                col.ColumnName = dtSchema2.Rows[i]["ColumnName"].ToString();
                                col.Ordinal = int.Parse(dtSchema2.Rows[i]["ColumnOrdinal"].ToString());
                                col.Size = 0;
                                try
                                {
                                    col.Size = int.Parse(dtSchema2.Rows[i]["ColumnSize"].ToString());
                                }
                                catch
                                {
                                    col.Size = 0;
                                }
                                col.Precision = 0;
                                try
                                {
                                    col.Precision = int.Parse(dtSchema2.Rows[i]["NumericPrecision"].ToString());
                                }
                                catch
                                {
                                    col.Precision = 0;
                                }
                                col.Scale = 0;
                                try
                                {
                                    col.Scale = int.Parse(dtSchema2.Rows[i]["NumericScale"].ToString());
                                }
                                catch
                                {
                                    col.Scale = 0;
                                }
                                col.AllowNull = (bool)dtSchema2.Rows[i]["AllowDBNull"];
                                col.IsIdentity = false;
                                string dbTypeStr = dtSchema2.Rows[i]["DataType"].ToString();
                                col.DbType = FieldDataType.ToDbType(dbTypeStr);
                                try
                                {
                                    bool isKey = (bool)dtSchema2.Rows[i]["IsKey"];
                                    bool isUnique = (bool)dtSchema2.Rows[i]["IsUnique"];
                                    if (isKey && isUnique)
                                    {
                                        col.IsPK = true;
                                    }
                                    else
                                    {
                                        col.IsPK = false;
                                    }
                                }
                                catch
                                {
                                    col.IsPK = false;
                                }
                                cols.Add(col);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        conn2.Close();
                    }
                    break;
            }

            if (cols.Count > 0)
            {
                return cols.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="spName"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static TableColumn[] GetTableColumnsFromSelectionStoredProcedure(string connStr, string spName, SqlParameter[] sqlParams)
        {
            List<TableColumn> cols = new List<TableColumn>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 60;    // set timeout to 1 min
                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader1 = cmd.ExecuteReader();
                DataTable dtSchema1 = reader1.GetSchemaTable();
                reader1.Close();
                if (dtSchema1 != null && dtSchema1.Rows.Count > 0)
                {
                    for (int i = 0; i < dtSchema1.Rows.Count; i++)
                    {
                        TableColumn col = new TableColumn();
                        col.ColumnName = dtSchema1.Rows[i]["ColumnName"].ToString();
                        col.Ordinal = int.Parse(dtSchema1.Rows[i]["ColumnOrdinal"].ToString());
                        col.Size = 0;
                        try
                        {
                            col.Size = int.Parse(dtSchema1.Rows[i]["ColumnSize"].ToString());
                        }
                        catch
                        {
                            col.Size = 0;
                        }
                        col.Precision = 0;
                        try
                        {
                            col.Precision = int.Parse(dtSchema1.Rows[i]["NumericPrecision"].ToString());
                        }
                        catch
                        {
                            col.Precision = 0;
                        }
                        col.Scale = 0;
                        try
                        {
                            col.Scale = int.Parse(dtSchema1.Rows[i]["NumericScale"].ToString());
                        }
                        catch
                        {
                            col.Scale = 0;
                        }
                        col.AllowNull = (bool)dtSchema1.Rows[i]["AllowDBNull"];
                        col.IsIdentity = (bool)dtSchema1.Rows[i]["IsIdentity"];
                        string dbTypeStr = dtSchema1.Rows[i]["DataType"].ToString();
                        if (dbTypeStr == typeof(string).ToString() && col.Size > 8000)
                        {
                            col.DbType = SqlDbType.Text;
                        }
                        else
                        {
                            col.DbType = FieldDataType.ToDbType(dbTypeStr);
                        }
                        try
                        {
                            bool isKey = (bool)dtSchema1.Rows[i]["IsKey"];
                            bool isUnique = (bool)dtSchema1.Rows[i]["IsUnique"];
                            if (isKey && isUnique)
                            {
                                col.IsPK = true;
                            }
                            else
                            {
                                col.IsPK = false;
                            }
                        }
                        catch
                        {
                            col.IsPK = false;
                        }
                        col.IsFK = false;
                        col.IsIndexed = false;
                        cols.Add(col);
                    }
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (cols.Count > 0)
            {
                return cols.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <returns></returns>
        public static TableColumn GetTableColumn(string connStr, string tblName, string colName)
        {
            TableColumn col = new TableColumn();
            TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, string.Empty, tblName);
            if (cols == null)
                throw new Exception("Unable to access table " + tblName);
            for (int i = 0; i < cols.Length; i++)
            {
                if (cols[i].ColumnName.ToLower() == colName.ToLower())
                {
                    col = cols[i];
                    break;
                }
            }
            return col;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static TableColumn GetTableColumn(DataProviderType dbProviderType, string connStr, string schemaName, string tableName, string fieldName)
        {
            TableColumn col = new TableColumn();
            TableColumn[] cols = DbUtil.GetTableColumns(dbProviderType, connStr, schemaName, tableName);
            if (cols == null)
                throw new Exception("Unable to access table " + tableName);
            for (int i = 0; i < cols.Length; i++)
            {
                if (cols[i].ColumnName.ToLower() == fieldName.ToLower())
                {
                    col = cols[i];
                    break;
                }
            }
            return col;
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="sql"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static TableColumn GetTableColumn(DataProviderType dbProviderType, string connStr, string sql, string fieldName)
        {
            TableColumn col = new TableColumn();
            TableColumn[] cols = DbUtil.GetTableColumns(dbProviderType, connStr, sql);
            if (cols == null)
                throw new Exception("Unable to access database schema");
            for (int i = 0; i < cols.Length; i++)
            {
                if (cols[i].ColumnName.ToLower() == fieldName.ToLower())
                {
                    col = cols[i];
                    break;
                }
            }
            return col;
        }

		/// <summary>
		/// 
		/// </summary>
		/// <param name="dataProviderType"></param>
		/// <param name="connStr"></param>
		/// <param name="spName"></param>
		/// <returns></returns>
		public static TableColumn[] GetTableColumnsFromStoredProc(DataProviderType dataProviderType, string connStr, string spName)
		{
			List<TableColumn> cols = new List<TableColumn>();
			switch (dataProviderType)
			{
				case DataProviderType.MSSQL:
					SqlConnection conn1 = new SqlConnection(connStr);
					try
					{
						conn1.Open();
						SqlCommand cmd1 = conn1.CreateCommand();
						cmd1.CommandTimeout = 60;    // set timeout to 1 min
						cmd1.CommandType = CommandType.StoredProcedure;
						cmd1.CommandText = spName;
						SqlDataReader reader1 = cmd1.ExecuteReader();
						DataTable dtSchema1 = reader1.GetSchemaTable();
						reader1.Close();
						if (dtSchema1 != null && dtSchema1.Rows.Count > 0)
						{
							for (int i = 0; i < dtSchema1.Rows.Count; i++)
							{
								TableColumn col = new TableColumn();
								col.ColumnName = dtSchema1.Rows[i]["ColumnName"].ToString();
								col.Ordinal = int.Parse(dtSchema1.Rows[i]["ColumnOrdinal"].ToString());
								col.Size = 0;
								try
								{
									col.Size = int.Parse(dtSchema1.Rows[i]["ColumnSize"].ToString());
								}
								catch
								{
									col.Size = 0;
								}
								col.Precision = 0;
								try
								{
									col.Precision = int.Parse(dtSchema1.Rows[i]["NumericPrecision"].ToString());
								}
								catch
								{
									col.Precision = 0;
								}
								col.Scale = 0;
								try
								{
									col.Scale = int.Parse(dtSchema1.Rows[i]["NumericScale"].ToString());
								}
								catch
								{
									col.Scale = 0;
								}
								col.AllowNull = (bool)dtSchema1.Rows[i]["AllowDBNull"];
								col.IsIdentity = (bool)dtSchema1.Rows[i]["IsIdentity"];
								string dbTypeStr = dtSchema1.Rows[i]["DataType"].ToString();
								if (dbTypeStr == typeof(string).ToString() && col.Size > 8000)
								{
									col.DbType = SqlDbType.Text;
								}
								else
								{
									col.DbType = FieldDataType.ToDbType(dbTypeStr);
								}
								try
								{
									bool isKey = (bool)dtSchema1.Rows[i]["IsKey"];
									bool isUnique = (bool)dtSchema1.Rows[i]["IsUnique"];
									if (isKey && isUnique)
									{
										col.IsPK = true;
									}
									else
									{
										col.IsPK = false;
									}
								}
								catch
								{
									col.IsPK = false;
								}
								cols.Add(col);
							}
						}
					}
					catch (Exception ex)
					{
						ExceptionHandler.HandlesException(ex);
					}
					finally
					{
						conn1.Close();
					}
					break;
				case DataProviderType.Oracle:
					OracleConnection conn2 = new OracleConnection(connStr);
					try
					{
						conn2.Open();
						OracleCommand cmd2 = conn2.CreateCommand();
						((IDbCommand)cmd2).CommandTimeout = 600;    // set timeout to 10 min
						cmd2.CommandType = CommandType.StoredProcedure;
						cmd2.CommandText = spName;

						OracleDataReader reader2 = cmd2.ExecuteReader();
						DataTable dtSchema2 = reader2.GetSchemaTable();
						reader2.Close();
						if (dtSchema2 != null && dtSchema2.Rows.Count > 0)
						{
							for (int i = 0; i < dtSchema2.Rows.Count; i++)
							{
								TableColumn col = new TableColumn();
								col.ColumnName = dtSchema2.Rows[i]["ColumnName"].ToString();
								col.Ordinal = int.Parse(dtSchema2.Rows[i]["ColumnOrdinal"].ToString());
								col.Size = 0;
								try
								{
									col.Size = int.Parse(dtSchema2.Rows[i]["ColumnSize"].ToString());
								}
								catch
								{
									col.Size = 0;
								}
								col.Precision = 0;
								try
								{
									col.Precision = int.Parse(dtSchema2.Rows[i]["NumericPrecision"].ToString());
								}
								catch
								{
									col.Precision = 0;
								}
								col.Scale = 0;
								try
								{
									col.Scale = int.Parse(dtSchema2.Rows[i]["NumericScale"].ToString());
								}
								catch
								{
									col.Scale = 0;
								}
								col.AllowNull = (bool)dtSchema2.Rows[i]["AllowDBNull"];
								col.IsIdentity = false;
								string dbTypeStr = dtSchema2.Rows[i]["DataType"].ToString();
								col.DbType = FieldDataType.ToDbType(dbTypeStr);
								try
								{
									bool isKey = (bool)dtSchema2.Rows[i]["IsKey"];
									bool isUnique = (bool)dtSchema2.Rows[i]["IsUnique"];
									if (isKey && isUnique)
									{
										col.IsPK = true;
									}
									else
									{
										col.IsPK = false;
									}
								}
								catch
								{
									col.IsPK = false;
								}
								cols.Add(col);
							}
						}
					}
					catch (Exception ex)
					{
						ExceptionHandler.HandlesException(ex);
					}
					finally
					{
						conn2.Close();
					}
					break;
			}

			if (cols.Count > 0)
			{
				return cols.ToArray();
			}
			else
			{
				return null;
			}
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static List<string> GetTablePrimaryKeys(string connStr, string schemaName, string tblName)
        {
            if (string.IsNullOrEmpty(schemaName))
                schemaName = "dbo";

            List<string> pkFieldNames = new List<string>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select Table_Schema, Column_Name from Information_Schema.Constraint_Column_Usage where " +
                    "Constraint_Name in (select Constraint_Name from Information_Schema.Table_Constraints where " +
                    "Table_Name='" + tblName + "' and Constraint_Type='Primary Key')";
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string schema = reader["Table_Schema"].ToString();
                    if(schema.ToLower()!=schemaName.ToLower())
                        continue;
                    string keyFieldName = reader["Column_Name"].ToString();
                    if (!pkFieldNames.Contains(keyFieldName))
                        pkFieldNames.Add(keyFieldName);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            if (pkFieldNames.Count > 0)
            {
                return pkFieldNames;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static System.Data.Constraint[] GetTableConstraints(string connStr, string tblName)
        {
            List<Constraint> constraints = new List<Constraint>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlDataAdapter daSelect = new SqlDataAdapter("select top 1 * from [" + tblName + "]", conn);
                System.Data.DataSet dsSchema = new DataSet();
                daSelect.Fill(dsSchema);
                System.Data.DataTable dtSchema = dsSchema.Tables[0];
                foreach (Constraint constraint in dtSchema.Constraints)
                {
                    constraints.Add(constraint);
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            if (constraints.Count > 0)
            {
                return constraints.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static string[] GetTablePrimaryKeyConstraintNames(string connStr, string tblName)
        {
            List<string> pkConstraintNames = new List<string>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select Constraint_Name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS " +
                    "where Table_Name='" + tblName + "' and Constraint_Type='Primary Key'";
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string constraint_name = reader[0].ToString();
                    pkConstraintNames.Add(constraint_name);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            if (pkConstraintNames.Count > 0)
            {
                return pkConstraintNames.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static long GetTableRecordCount(string connStr, string tblName)
        {
            long size = 0;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 5*60;
                cmd.CommandText = "select count (*) from [" + tblName + "]";
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    size = long.Parse(reader[0].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return size;
        }
        #endregion

        #region constraints
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <param name="idxName"></param>
        public static void AddIndex(string connStr, string tblName, string colName, string idxName)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 2 * 60 * 60; // 2 hr
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("create index {0} on {1} ({2})", idxName, tblName, colName);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <returns></returns>
        public static bool IsTableColumnForeignKey(string connStr, string tblName, string colName)
        {
            string[] fks = DbUtil.GetTableForeignKeyColumnNames(connStr, "", tblName);
            if (fks != null && fks.Length > 0)
            {
                for (int i = 0; i < fks.Length; i++)
                {
                    if (fks[i].ToLower() == colName.ToLower())
                        return true;
                }
                return false;
            }
            else
                return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <returns></returns>
        public static bool IsTableColumnIndexed(string connStr, string tblName, string colName)
        {
            string[] idxCols = DbUtil.GetTableIndexedColumnNames(connStr, tblName);
            if (idxCols != null && idxCols.Length > 0)
            {
                for (int i = 0; i < idxCols.Length; i++)
                {
                    if (idxCols[i].ToLower() == colName.ToLower())
                        return true;
                }
                return false;
            }
            else
                return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static string[] GetTableForeignKeyColumnNames(string connStr, string schemaName, string tblName)
        {
            if (string.IsNullOrEmpty(schemaName))
                schemaName = "dbo";
            string getFkSql = @"
SELECT 
    FK_Table  = FK.TABLE_NAME, 
    FK_Column = CU.COLUMN_NAME, 
    PK_Table  = PK.TABLE_NAME, 
    PK_Column = PT.COLUMN_NAME, 
    FK.Constraint_Schema,
    Constraint_Name = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
        SELECT 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            INNER JOIN 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME 
WHERE 
    FK.TABLE_NAME='" + tblName + "'";

            List<string> fks = new List<string>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = getFkSql;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string schema = reader["Constraint_Schema"].ToString();
                    if(schema.ToLower()!=schemaName.ToLower())
                        continue;
                    string fk = reader["FK_Column"].ToString();
                    fks.Add(fk);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (fks.Count > 0)
                return fks.ToArray();
            else
                return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static TableReference[] GetParentTableReferences(string connStr, string tblName)
        {
            string getFkSql = @"
SELECT 
    FK_Table  = FK.TABLE_NAME, 
    FK_Column = CU.COLUMN_NAME, 
    PK_Table  = PK.TABLE_NAME, 
    PK_Column = PT.COLUMN_NAME, 
    Constraint_Name = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
        SELECT 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            INNER JOIN 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME 
WHERE 
    FK.TABLE_NAME='" + tblName + "'";

            List<TableReference> tblRefs = new List<TableReference>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = getFkSql;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    TableReference tblRef = new TableReference("", "", "", "");
                    tblRef.ParentTableName = reader["PK_Table"].ToString();
                    tblRef.ParentColumnName = reader["PK_Column"].ToString();
                    tblRef.ChildTableName = reader["FK_Table"].ToString();
                    tblRef.ChildColumnName = reader["FK_Column"].ToString();
                    tblRefs.Add(tblRef);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (tblRefs.Count > 0)
                return tblRefs.ToArray();
            else
                return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static TableReference[] GetChildTableReferences(string connStr, string tblName)
        {
            string getFkSql = @"
SELECT 
    FK_Table  = FK.TABLE_NAME, 
    FK_Column = CU.COLUMN_NAME, 
    PK_Table  = PK.TABLE_NAME, 
    PK_Column = PT.COLUMN_NAME, 
    Constraint_Name = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
        SELECT 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            INNER JOIN 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME 
WHERE 
    PK.TABLE_NAME='" + tblName + "'";

            List<TableReference> tblRefs = new List<TableReference>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = getFkSql;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    TableReference tblRef =new TableReference("","","","");
                    tblRef.ParentTableName = reader["PK_Table"].ToString();
                    tblRef.ParentColumnName = reader["PK_Column"].ToString();
                    tblRef.ChildTableName = reader["FK_Table"].ToString();
                    tblRef.ChildColumnName = reader["FK_Column"].ToString();
                    tblRefs.Add(tblRef);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (tblRefs.Count > 0)
                return tblRefs.ToArray();
            else
                return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName1"></param>
        /// <param name="tblName2"></param>
        /// <returns></returns>
        public static TableReference GetTableReference(string connStr, string tblName1, string tblName2)
        {
            TableReference tblRef = new TableReference("","","","");
            string whereClause = "(PK.Table_Name='" + tblName1 + "' and FK.Table_Name='" + tblName2 + "') or " +
                                 "(PK.Table_Name='" + tblName2 + "' and FK.Table_Name='" + tblName1 + "')";
            string getFkSql =
                @"
SELECT 
    FK_Table  = FK.TABLE_NAME, 
    FK_Column = CU.COLUMN_NAME, 
    PK_Table  = PK.TABLE_NAME, 
    PK_Column = PT.COLUMN_NAME, 
    Constraint_Name = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
        SELECT 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            INNER JOIN 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME 
WHERE " +
                whereClause;

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = getFkSql;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    tblRef.ParentTableName = reader["PK_Table"].ToString();
                    tblRef.ParentColumnName = reader["PK_Column"].ToString();
                    tblRef.ChildTableName = reader["FK_Table"].ToString();
                    tblRef.ChildColumnName = reader["FK_Column"].ToString();
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return tblRef;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="childTable"></param>
        /// <returns></returns>
        public static List<TableReference> GetTableReference(
            DataProviderType dbProvider,
            string connStr, string schemaName, string childTable)
        {
            List<TableReference> tblRefs=new List<TableReference>();
            string sql = "";
            IDbConnection conn = null;
            if(dbProvider==DataProviderType.MSSQL)
            {
                conn = new SqlConnection(connStr);
                sql =
                    @"SELECT 
	FSCHEMA = FK.CONSTRAINT_SCHEMA,
    FTable = FK.TABLE_NAME, 
    FK = CU.COLUMN_NAME, 
	PSCHEMA=PK.CONSTRAINT_SCHEMA,
    PTable  = PK.TABLE_NAME, 
    PK = PT.COLUMN_NAME, 
    Constraint_Name = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
        SELECT 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE 
    FK.CONSTRAINT_SCHEMA='{0}' and
	FK.TABLE_NAME='{1}'";
                sql = string.Format(sql, schemaName, childTable);
            }
            else
            {
                conn = new OracleConnection(connStr);
                sql = @"SELECT 
       A.OWNER AS FSCHEMA, 
       A.TABLE_NAME AS FTABLE, 
       A.COLUMN_NAME AS FK, 
       A.CONSTRAINT_NAME, 
       DECODE(B.CONSTRAINT_TYPE, 
       'P','PRIMARY KEY',
       'U','UNIQUE', 
       'C','CHECK',
       'D','DEFAULT',
       'R','FOREIGN KEY') AS CONSTRAINT_TYPE,
       D.OWNER AS PSCHEMA,
       D.TABLE_NAME AS PTABLE,
       D.COLUMN_NAME AS PK 
FROM 
       SYS.ALL_CONS_COLUMNS A, SYS.ALL_CONSTRAINTS B 
       LEFT JOIN SYS.ALL_CONS_COLUMNS D ON D.CONSTRAINT_NAME=B.R_CONSTRAINT_NAME
WHERE 
    A.CONSTRAINT_NAME=B.CONSTRAINT_NAME 
    AND UPPER(A.OWNER) = '{0}'
    AND UPPER(A.TABLE_NAME) ='{1}'
     AND B.CONSTRAINT_TYPE='R'";
                sql = string.Format(sql, schemaName.ToUpper(), childTable.ToUpper());
            }
            string dbName = DbConnUtil.GetDatabaseName(connStr);
            try
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                IDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    string pSchema = reader["PSCHEMA"].ToString();
                    string pTable = reader["PTABLE"].ToString();
                    string pk = reader["PK"].ToString();
                    string fSchema = reader["FSCHEMA"].ToString();
                    string fTable = reader["FTABLE"].ToString();
                    string fk = reader["FK"].ToString();
                    TableReference tblRef = new TableReference(dbName, pSchema, pTable, pk, dbName, fSchema, fTable, fk);
                    tblRefs.Add(tblRef);
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return tblRefs;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="schema1"></param>
        /// <param name="table1"></param>
        /// <param name="schema2"></param>
        /// <param name="table2"></param>
        /// <returns></returns>
        public static TableReference GetTableReference(string connStr, string schema1, string table1, string schema2, string table2)
        {
            if (string.IsNullOrEmpty(schema1))
                schema1 = "dbo";
            if (string.IsNullOrEmpty(schema2))
                schema2 = "dbo";

            TableReference tblRef = new TableReference("", "", "", "");
            string whereClause = "(PK.Table_Name='" + table1 + "' and FK.Table_Name='" + table2 + "') or " +
                                 "(PK.Table_Name='" + table2 + "' and FK.Table_Name='" + table1 + "')";
            string getFkSql =
                @"
SELECT 
    FK_Table  = FK.TABLE_NAME, 
    FK_Column = CU.COLUMN_NAME, 
    PK_Table  = PK.TABLE_NAME, 
    PK_Column = PT.COLUMN_NAME, 
    Constraint_Name = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
        SELECT 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            INNER JOIN 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME 
WHERE " +
                whereClause;

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = getFkSql;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    tblRef.ParentTableName = reader["PK_Table"].ToString();
                    tblRef.ParentColumnName = reader["PK_Column"].ToString();
                    tblRef.ChildTableName = reader["FK_Table"].ToString();
                    tblRef.ChildColumnName = reader["FK_Column"].ToString();
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return tblRef;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static string[] GetTableIndexedColumnNames(
            string connStr, string tblName)
        {
            string getIndexSql = "select name as ColumnName from " +
                                 "sys.columns where column_id in (" +
                                 "select column_id from sys.index_columns where " +
                                 "object_id=OBJECT_ID('[" + tblName + "]'))" +
                                 "and object_id=OBJECT_ID('[" + tblName + "]')";
            List<string> indexedColumns = new List<string>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = getIndexSql;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string indexedColumn = reader.GetString(0);
                    indexedColumns.Add(indexedColumn);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            if (indexedColumns.Count > 0)
                return indexedColumns.ToArray();
            else
                return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static string[] GetTableIndexedColumnNames(DataProviderType dbProvider, 
            string connStr, string schemaName, string tblName)
        {
            if (dbProvider == DataProviderType.MSSQL)
                return DbUtil.GetTableIndexedColumnNames(connStr, tblName);
            else
            {
                List<string> colNames=new List<string>();
                OracleConnection conn=new OracleConnection(connStr);
                string sql = @"
SELECT A.TABLE_OWNER, A.TABLE_NAME, A.COLUMN_NAME FROM SYS.ALL_IND_COLUMNS A 
WHEREUPPER(A.TABLE_OWNER)='{0}' AND  UPPER(A.TABLE_NAME) = '{1}'";
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.CommandText = string.Format(sql, schemaName.ToUpper(), tblName.ToUpper());
                    cmd.CommandType = CommandType.Text;
                    OracleDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        colNames.Add(reader["COLUMN_NAME"].ToString());
                    }
                    reader.Close();
                }
                catch(Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
                return colNames.ToArray();
            }
        }

        /// <summary>
        /// index name -> column names
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static Dictionary<string,List<string>> GetIndexNamesForTable(string connStr, string tblName)
        {
            string getIndexSql = @"SELECT DISTINCT 
A.name as IndexName, 
A.[type] as IndexType, 
C.name as ColumnName, 
A.type_desc 
FROM sys.indexes A, sys.index_columns B, sys.all_columns C  
WHERE A.index_id=B.index_id AND A.object_id=B.object_id and C.column_id=B.column_id and C.object_id=B.object_id
AND A.object_id=OBJECT_ID('{0}')";
            Dictionary<string, List<string>> indexColumnNames = 
                new Dictionary<string, List<string>>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = string.Format(getIndexSql, tblName);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string indexName = reader["IndexName"].ToString();
                    string columnName = reader["ColumnName"].ToString();
                    if (indexColumnNames.ContainsKey(indexName))
                    {
                        List<string> colNames = indexColumnNames[indexName];
                        colNames.Add(columnName);
                        indexColumnNames[indexName] = colNames;
                    }
                    else
                    {
                        List<string> colNames=new List<string>();
                        colNames.Add(columnName);
                        indexColumnNames.Add(indexName, colNames);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return indexColumnNames;
        }
        #endregion

        #region get data
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static DataTable GetTableData(string connStr, string tblName)
        {
            DataTable dtOutput = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                string sql = "select * from [" + tblName + "]";
                SqlDataAdapter daSelect = new SqlDataAdapter(sql, conn);
                DataSet dsOutput = new DataSet();
                daSelect.Fill(dsOutput, tblName);
                dtOutput = dsOutput.Tables[0];
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtOutput;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="outputTableName"></param>
        /// <param name="selectSQL"></param>
        /// <returns></returns>
        public static DataTable GetTableDataBySQL(
            string connStr, string outputTableName, string selectSQL)
        {
            DataTable dtOutput = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlDataAdapter daSelect = new SqlDataAdapter(selectSQL, conn);
                DataSet dsOutput = new DataSet();
                daSelect.Fill(dsOutput, outputTableName);
                dtOutput = dsOutput.Tables[0];
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtOutput;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="selectSQL"></param>
        /// <param name="pct"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static DataTable GetTableDataSampleBySQL(
            string connStr, string tblName, string selectSQL, 
            int pct, int count)
        {
            int recordCount = DbUtil.GetTableRecordCount(DataProviderType.MSSQL, connStr, selectSQL);
            int limit = 0;
            if (count > 0 && count < recordCount)
                limit = count;
            else if (pct > 0 && pct < 100)
                limit = recordCount*pct/100;
            DataTable dtOutput = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                if(limit>0)
                {
                    cmd.CommandText = string.Format("SET ROWCOUNT {0}", limit);
                    cmd.ExecuteNonQuery();
                }
                SqlDataAdapter daSelect = new SqlDataAdapter(selectSQL, conn);
                DataSet dsOutput = new DataSet();
                daSelect.Fill(dsOutput, tblName);
                dtOutput = dsOutput.Tables[0];
                if (limit > 0)
                {
                    cmd.CommandText = string.Format("SET ROWCOUNT {0}", 0);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtOutput;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="previewRowCount"></param>
        /// <returns></returns>
        public static DataTable GetTableData(string connStr, string tblName, int previewRowCount)
        {
            DataTable dtOutput = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                string sql = "select top " + previewRowCount + " * from [" + tblName + "]";
                SqlDataAdapter daSelect = new SqlDataAdapter(sql, conn);
                DataSet dsOutput = new DataSet();
                daSelect.Fill(dsOutput, tblName);
                dtOutput = dsOutput.Tables[0];
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtOutput;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="selectStatement"></param>
        /// <returns></returns>
        public static DataTable GetTableDataUsingSelectStatement(string connStr, string selectStatement)
        {
            DataTable dtOutput = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlDataAdapter daSelect = new SqlDataAdapter(selectStatement, conn);
                DataSet dsOutput = new DataSet();
                daSelect.Fill(dsOutput);
                dtOutput = dsOutput.Tables[0];
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtOutput;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="spName"></param>
        /// <param name="sqlParams"></param>
        /// <returns></returns>
        public static DataTable GetTableDataFromSelectStoredProcedure(string connStr, string spName, SqlParameter[] sqlParams)
        {
            DataTable dtOutput = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;
                if(sqlParams !=null && sqlParams.Length>0)
                {
                    cmd.Parameters.AddRange(sqlParams);
                }
                SqlDataAdapter daSelect = new SqlDataAdapter(cmd);
                DataSet dsOutput = new DataSet();
                daSelect.Fill(dsOutput);
                dtOutput = dsOutput.Tables[0];
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtOutput;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="pkFieldName"></param>
        /// <param name="pageNum"></param>
        /// <param name="recordsPerPage"></param>
        /// <returns></returns>
        public static DataTable GetPagedTableData(string connStr, string tblName, string pkFieldName, int pageNum, int recordsPerPage)
        {
            DataTable dtOutput = null;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                int fromNumber = pageNum*recordsPerPage + 1;
                int toNumber = fromNumber + recordsPerPage;
                string sql = "select * from (select Row_Number() over (order by [" +
                             pkFieldName + "] asc) as RowNum, * from [" + tblName + "]) orderedTable " +
                             "where RowNum between " + fromNumber + " and " + toNumber;
                SqlDataAdapter daSelect = new SqlDataAdapter(sql, conn);
                DataSet dsOutput = new DataSet();
                daSelect.Fill(dsOutput, tblName);
                dtOutput = dsOutput.Tables[0];
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtOutput;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="spName"></param>
        /// <param name="parameters"></param>
        /// <param name="pageNum"></param>
        /// <param name="recordsPerPage"></param>
        /// <returns></returns>
        public static DataTable GetPagedTableData(string connStr, string spName, SqlParameter[] parameters, int pageNum, int recordsPerPage)
        {
            DataTable dtOutput = null;
            SqlConnection conn = new SqlConnection(connStr);
            TableColumn[] cols = DbUtil.GetTableColumnsFromSelectionStoredProcedure(connStr, spName, parameters);
            dtOutput = new DataTable();
            for(int i=0;i<cols.Length;i++)
            {
                dtOutput.Columns.Add(cols[i].ColumnName, FieldDataType.ToSystemType(cols[i].DbType));
            }
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                SqlDataReader reader = cmd.ExecuteReader();
                int index = 0;
                int fromNumber = pageNum * recordsPerPage + 1;
                int toNumber = fromNumber + recordsPerPage;

                while(reader.Read())
                {
                    index += 1;
                    if(index>toNumber)
                        break;
                    if(index >=fromNumber)
                    {
                        DataRow drOutput = dtOutput.NewRow();
                        for(int i=0;i<cols.Length;i++)
                        {
                            drOutput[cols[i].ColumnName] = reader[cols[i].ColumnName];
                        }
                        dtOutput.Rows.Add(drOutput);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return dtOutput;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <param name="total"></param>
        /// <param name="isRandom"></param>
        /// <returns></returns>
        public static List<object> SampleTableFieldData(DataProviderType dbProvider, string connStr, string schemaName, string tblName, string colName, int total, bool isRandom)
        {
            List<object> fieldData=new List<object>();
            if(dbProvider==DataProviderType.Oracle)
            {
                OracleConnection conn=new OracleConnection(connStr);
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    string sql = @"SELECT {0} FROM
( SELECT {0} FROM {1}.[{2}]
ORDER BY {3} )
WHERE rownum = {4}";
                    cmd.CommandText =
                        string.Format(
                            sql, colName, 
                            string.IsNullOrEmpty(schemaName)?"":schemaName, 
                            tblName,
                            (isRandom ? "dbms_random.value" : colName), total);
                    OracleDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader[0] != null && reader[0] != DBNull.Value)
                        {
                            fieldData.Add(reader[0]);
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                SqlConnection conn = new SqlConnection(connStr);
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format(
                        "select top {0} [{1}] from [{2}].[{3}] order by {4}",
                        total, colName,
                        string.IsNullOrEmpty(schemaName) ? "dbo" : schemaName,
                        tblName,
                        (isRandom ? "NEWID()" : "[" + colName + "]"));
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        if (reader[0] != null && reader[0] != DBNull.Value)
                        {
                            fieldData.Add(reader[0]);
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            
            return fieldData;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <param name="fieldValues"></param>
        /// <returns></returns>
        public static bool TableFieldContainsAll(DataProviderType dbProvider, string connStr, string schemaName, string tblName, string colName, List<object> fieldValues)
        {
            IDbConnection conn=null;
            if (dbProvider == DataProviderType.Oracle)
                conn = new OracleConnection(connStr);
            else
                conn = new SqlConnection(connStr);
            bool containsAll = true;
            TableColumn col = DbUtil.GetTableColumn(dbProvider, connStr, schemaName, tblName, colName);
            try
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                string sql = "select count (*) from [{0}].[{1}] where [{2}]={3}";
                foreach (object val in fieldValues)
                {
                    if (dbProvider == DataProviderType.Oracle)
                    {
                        cmd.CommandText = string.Format(
                            sql,
                            string.IsNullOrEmpty(schemaName) ? "" : schemaName,
                            tblName, colName,
                            FieldDataType.ToOracleDataValue(
                                col.DbType, val.ToString()));
                    }
                    else
                    {
                        cmd.CommandText = string.Format(
                            sql,
                            string.IsNullOrEmpty(schemaName) ? "dbo" : schemaName,
                            tblName, colName,
                            FieldDataType.ToMssqlDataValue(
                                col.DbType, val.ToString()));
                    }
                    IDataReader reader = cmd.ExecuteReader();
                    if(reader.Read())
                    {
                        int count = 0;
                        bool canRead = int.TryParse(reader[0].ToString(), out count);
                        if(!canRead || count==0)
                        {
                            containsAll = false;
                        }
                    }
                    else
                    {
                        containsAll = false;
                    }
                    reader.Close();
                    if(!containsAll)
                        break;
                }
            }
            catch (Exception)
            {
                containsAll = false;
            }
            finally
            {
                conn.Close();
            }
            return containsAll;
        }
        #endregion

        #region get column data stats
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <param name="sampleSize"></param>
        /// <returns></returns>
        public static Database.TableFieldValueStatistics GetColumnDataStatistics(string connStr, string tblName, string colName, int sampleSize)
        {
            Database.TableFieldValueStatistics colDataStats = new Database.TableFieldValueStatistics();

            TableColumn[] tblColumns = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, string.Empty, tblName);
            TableColumn tblCol = new TableColumn();
            for (int i = 0; i < tblColumns.Length; i++)
            {
                if (tblColumns[i].ColumnName.ToLower() == colName.ToLower())
                {
                    tblCol = tblColumns[i];
                    break;
                }
            }
            int recordCount = (int)DbUtil.GetTableRecordCount(connStr, tblName);
            List<int> indexList = new List<int>();
            if (recordCount > sampleSize)
            {
                System.Random randomGen = new Random();
                for (int i = 0; i < sampleSize; i++)
                {
                    int idx = randomGen.Next(recordCount);
                    indexList.Add(idx);
                }
            }
            else
            {
                for (int i = 0; i < recordCount; i++)
                {
                    indexList.Add(i);
                }
            }

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                string sql = "select [" + colName + "] from [" + tblName + "]";
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                SqlDataReader reader = cmd.ExecuteReader();
                int index = 0;
                int total = 0;
                int missingValue = 0;

                if (FieldDataType.IsNumber(tblCol.DbType))
                {
                    List<decimal> numberElements = new List<decimal>();
                    Dictionary<decimal, int> numberSpread = new Dictionary<decimal, int>();
                    decimal numberSum = 0;

                    while (reader.Read())
                    {
                        if (indexList.Contains(index))
                        {
                            decimal numberValue = 0;
                            try
                            {
                                numberValue = decimal.Parse(reader[0].ToString());
                                numberSum += numberValue;
                            }
                            catch
                            {
                                missingValue += 1;
                                numberValue = 0;
                            }
                            numberElements.Add(numberValue);
                            if (numberSpread.ContainsKey(numberValue))
                            {
                                int count = numberSpread[numberValue];
                                numberSpread[numberValue] = count + 1;
                            }
                            else
                            {
                                numberSpread.Add(numberValue, 1);
                            }

                            total += 1;
                        }
                        index++;
                    }
                    reader.Close();
                    numberElements.Sort();
                    List<Database.DistinctFieldValueCount> distinctValueCounts = new List<Database.DistinctFieldValueCount>();
                    if (numberSpread.Count > 0)
                    {
                        decimal[] distinctNumbers = new decimal[numberSpread.Count];
                        numberSpread.Keys.CopyTo(distinctNumbers, 0);
                        foreach (decimal distinctNumber in distinctNumbers)
                        {
                            Database.DistinctFieldValueCount distinctValueCountPair = new Database.DistinctFieldValueCount();
                            distinctValueCountPair.Value = distinctNumber.ToString();
                            distinctValueCountPair.Count = numberSpread[distinctNumber];
                            distinctValueCounts.Add(distinctValueCountPair);
                        }
                        colDataStats.distinctValueCountPairs = distinctValueCounts.ToArray();
                        colDataStats.Max = numberElements[numberElements.Count - 1].ToString();
                        colDataStats.Min = numberElements[0].ToString();
                        colDataStats.Mean = "0";
                        if (total - missingValue > 0)
                        {
                            colDataStats.Mean = (numberSum / (total - missingValue)).ToString();
                        }
                        if (total % 2 == 0)
                        {
                            colDataStats.Median = numberElements[total / 2 - 1].ToString();
                        }
                        else
                        {
                            colDataStats.Median = numberElements[(total + 1) / 2 - 1].ToString();
                        }
                    }
                }
                else if (FieldDataType.IsDateTime(tblCol.DbType))
                {
                }
                else
                {
                    List<string> strElements = new List<string>();
                    Dictionary<string, int> strSpread = new Dictionary<string, int>();

                    while (reader.Read())
                    {
                        if (indexList.Contains(index))
                        {
                            string strValue=string.Empty;
                            try
                            {
                                strValue = reader[0].ToString();
                            }
                            catch
                            {
                                missingValue += 1;
                                strValue = string.Empty;
                            }
                            strElements.Add(strValue);
                            if (strSpread.ContainsKey(strValue))
                            {
                                int count = strSpread[strValue];
                                strSpread[strValue] = count + 1;
                            }
                            else
                            {
                                strSpread.Add(strValue, 1);
                            }

                            total += 1;
                        }
                        index++;
                    }
                    reader.Close();
                    strElements.Sort();
                    List<Database.DistinctFieldValueCount> distinctValueCounts = new List<Database.DistinctFieldValueCount>();
                    if (strSpread.Count > 0)
                    {
                        string[] distinctStrs = new string[strSpread.Count];
                        strSpread.Keys.CopyTo(distinctStrs, 0);
                        foreach (string distinctStr in distinctStrs)
                        {
                            Database.DistinctFieldValueCount distinctValueCountPair = new Database.DistinctFieldValueCount();
                            distinctValueCountPair.Value = distinctStr;
                            distinctValueCountPair.Count = strSpread[distinctStr];
                            distinctValueCounts.Add(distinctValueCountPair);
                        }
                        colDataStats.distinctValueCountPairs = distinctValueCounts.ToArray();
                        colDataStats.Max = string.Empty;
                        colDataStats.Min = string.Empty;
                        colDataStats.Mean = string.Empty;
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return colDataStats;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static int GetTableFieldDistinctValueCount(DataProviderType dbProviderType, string connStr, string schemaName, string tableName, string fieldName)
        {
            int distinctCount = 0;
            if (dbProviderType == DataProviderType.Oracle)
            {
                OracleConnection oraConn = new OracleConnection(connStr);
                try
                {
                    oraConn.Open();
                    OracleCommand cmd = oraConn.CreateCommand();
                    ((IDbCommand)cmd).CommandTimeout = 15*60;
                    cmd.CommandText = "select count (distinct " + fieldName + ") from " +
                                      (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName);
                    OracleDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        try
                        {
                            distinctCount = int.Parse(reader[0].ToString());
                        }
                        catch
                        {
                            distinctCount = 0;
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    oraConn.Close();
                }
            }
            else
            {
                SqlConnection sqlConn = new SqlConnection(connStr);
                try
                {
                    sqlConn.Open();
                    SqlCommand cmd = sqlConn.CreateCommand();
                    cmd.CommandTimeout = 5*60;
                    cmd.CommandText = "select count (distinct [" + fieldName + "]) from [" +
                                      (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName) +
                                      "]";
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        try
                        {
                            distinctCount = int.Parse(reader[0].ToString());
                        }
                        catch
                        {
                            distinctCount = 0;
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    sqlConn.Close();
                }
            }
            return distinctCount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static string GetTableFieldMaxValue(DataProviderType dbProviderType, string connStr, string schemaName, string tableName, string fieldName)
        {
            string maxValue = string.Empty;
            if (dbProviderType == DataProviderType.Oracle)
            {
                OracleConnection oraConn = new OracleConnection(connStr);
                try
                {
                    oraConn.Open();
                    OracleCommand cmd = oraConn.CreateCommand();
                    ((IDbCommand)cmd).CommandTimeout = 15 * 60;
                    cmd.CommandText = "select " + fieldName + " from " +
                                      (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName) +
                                      " order by " + fieldName + " desc where RowNum=1";
                    OracleDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        try
                        {
                            maxValue = reader[0].ToString();
                        }
                        catch
                        {
                            maxValue = string.Empty;
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    oraConn.Close();
                }
            }
            else
            {
                SqlConnection sqlConn = new SqlConnection(connStr);
                try
                {
                    sqlConn.Open();
                    SqlCommand cmd = sqlConn.CreateCommand();
                    cmd.CommandTimeout = 5 * 60;
                    cmd.CommandText = "select top 1 [" + fieldName + "] from [" +
                                      (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName) +
                                      "] order by [" + fieldName + "] desc";
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        try
                        {
                            maxValue = reader[0].ToString();
                        }
                        catch
                        {
                            maxValue = string.Empty;
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    sqlConn.Close();
                }
            }
            return maxValue;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static string GetTableFieldMinValue(DataProviderType dbProviderType, string connStr, string schemaName, string tableName, string fieldName)
        {
            string minValue = string.Empty;
            if (dbProviderType == DataProviderType.Oracle)
            {
                OracleConnection oraConn = new OracleConnection(connStr);
                try
                {
                    oraConn.Open();
                    OracleCommand cmd = oraConn.CreateCommand();
                    ((IDbCommand)cmd).CommandTimeout = 15 * 60;
                    cmd.CommandText = "select " + fieldName + " from " +
                                      (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName) +
                                      " order by " + fieldName + " where RowNum=1";
                    OracleDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        try
                        {
                            minValue = reader[0].ToString();
                        }
                        catch
                        {
                            minValue = string.Empty;
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    oraConn.Close();
                }
            }
            else
            {
                SqlConnection sqlConn = new SqlConnection(connStr);
                try
                {
                    sqlConn.Open();
                    SqlCommand cmd = sqlConn.CreateCommand();
                    cmd.CommandTimeout = 5 * 60;
                    cmd.CommandText = "select top 1 [" + fieldName + "] from [" +
                                      (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName) +
                                      "] order by [" + fieldName + "]";
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        try
                        {
                            minValue = reader[0].ToString();
                        }
                        catch
                        {
                            minValue = string.Empty;
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    sqlConn.Close();
                }
            }
            return minValue;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static string GetTableFieldAverageValue(DataProviderType dbProviderType, string connStr, string schemaName, string tableName, string fieldName)
        {
            string meanValue = string.Empty;
            TableColumn col = DbUtil.GetTableColumn(dbProviderType, connStr, schemaName, tableName, fieldName);
            if(FieldDataType.IsNumber(col.DbType))
            {
                if (dbProviderType == DataProviderType.Oracle)
                {
                    OracleConnection oraConn = new OracleConnection(connStr);
                    try
                    {
                        oraConn.Open();
                        OracleCommand cmd = oraConn.CreateCommand();
                        ((IDbCommand)cmd).CommandTimeout = 15 * 60;
                        cmd.CommandText = "select avg(" + fieldName + ") from " +
                                          (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName);
                        OracleDataReader reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            try
                            {
                                meanValue = reader[0].ToString();
                            }
                            catch
                            {
                                meanValue = string.Empty;
                            }
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        oraConn.Close();
                    }
                }
                else
                {
                    SqlConnection sqlConn = new SqlConnection(connStr);
                    try
                    {
                        sqlConn.Open();
                        SqlCommand cmd = sqlConn.CreateCommand();
                        cmd.CommandTimeout = 5 * 60;
                        cmd.CommandText = "select avg ([" + fieldName + "]) from [" +
                                          (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName) +
                                          "]";
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            try
                            {
                                meanValue = reader[0].ToString();
                            }
                            catch
                            {
                                meanValue = string.Empty;
                            }
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        sqlConn.Close();
                    }
                }
            }
            
            return meanValue;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static string GetTableFieldMedianValue(DataProviderType dbProviderType, string connStr, string schemaName, string tableName, string fieldName)
        {
            string medianValue = string.Empty;
            int recordCount = DbUtil.GetTableRecordCount(dbProviderType, connStr, schemaName, tableName);
            if(recordCount >0)
            {
                int mid = recordCount/2;
                if (mid==0)
                {
                    mid = 1;
                }
                if (dbProviderType == DataProviderType.Oracle)
                {
                    OracleConnection oraConn = new OracleConnection(connStr);
                    try
                    {
                        oraConn.Open();
                        OracleCommand cmd = oraConn.CreateCommand();
                        ((IDbCommand)cmd).CommandTimeout = 15 * 60;
                        cmd.CommandText = "select " + fieldName + " from " +
                                          (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName) +
                                          " order by " + fieldName + " where RowNum=" + mid;
                        OracleDataReader reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            try
                            {
                                medianValue = reader[0].ToString();
                            }
                            catch
                            {
                                medianValue = string.Empty;
                            }
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        oraConn.Close();
                    }
                }
                else
                {
                    SqlConnection sqlConn = new SqlConnection(connStr);
                    try
                    {
                        sqlConn.Open();
                        SqlCommand cmd = sqlConn.CreateCommand();
                        cmd.CommandTimeout = 5 * 60;
                        cmd.CommandText = "select max (" + fieldName + ") from (select top 50 percent [" + fieldName +
                                          "] from [" +
                                          (string.IsNullOrEmpty(schemaName) ? tableName : schemaName + "." + tableName) +
                                          "] order by [" + fieldName + "]) as Median";
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            try
                            {
                                medianValue = reader[0].ToString();
                            }
                            catch
                            {
                                medianValue = string.Empty;
                            }
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        ExceptionHandler.HandlesException(ex);
                    }
                    finally
                    {
                        sqlConn.Close();
                    }
                }
            }
            

            return medianValue;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static Dictionary<string, int> GetTableFieldCountByDistinctValue(DataProviderType dbProviderType, string connStr, string schemaName, string tableName, string fieldName)
        {
            Dictionary<string, int> countByValues = new Dictionary<string, int>();
            if(dbProviderType == DataProviderType.Oracle)
            {
                OracleConnection conn = new OracleConnection(connStr);
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    ((IDbCommand)cmd).CommandTimeout = 15*60;
                    cmd.CommandText = "select " + fieldName + ", count (*) as Count from " + tableName +
                                      " group by " + fieldName + " order by " + fieldName;
                    OracleDataReader reader = cmd.ExecuteReader();
                    while(reader.Read())
                    {
                        object fieldValue = reader[0];
                        string fieldValueString = (fieldValue == null ? string.Empty : fieldValue.ToString());
                        fieldValueString = fieldValueString.ToString();
                        int count = int.Parse(reader[1].ToString());
                        if(!countByValues.ContainsKey(fieldValueString))
                        {
                            countByValues.Add(fieldValueString, count);
                        }
                    }
                    reader.Close();
                }
                catch(Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                SqlConnection conn = new SqlConnection(connStr);
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandTimeout = 15 * 60;
                    cmd.CommandText = "select " + fieldName + ", count (*) as Count from " + tableName +
                                      " group by " + fieldName + " order by " + fieldName;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        object fieldValue = reader[0];
                        string fieldValueString = (fieldValue == null ? string.Empty : fieldValue.ToString());
                        fieldValueString = fieldValueString.ToString();
                        int count = int.Parse(reader[1].ToString());
                        if (!countByValues.ContainsKey(fieldValueString))
                        {
                            countByValues.Add(fieldValueString, count);
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return countByValues;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static Dictionary<Range<decimal>, int> GetTableFieldCountByNumberRange(DataProviderType dbProviderType, string connStr, string schemaName, string tableName, string fieldName)
        {
            Dictionary<Range<decimal>, int> countByValues = new Dictionary<Range<decimal>, int>();
            decimal min = 0;
            try
            {
                min = decimal.Parse(DbUtil.GetTableFieldMinValue(dbProviderType, connStr, schemaName, tableName, fieldName));
            }
            catch
            {
                min = 0;
            }

            decimal max = 0;
            try
            {
                max = decimal.Parse(DbUtil.GetTableFieldMaxValue(dbProviderType, connStr, schemaName, tableName, fieldName));
            }
            catch
            {
                max = 0;
            }
            List<Range<decimal>> ranges = new List<Range<decimal>>();
            decimal interval = (max - min)/10;
            for (int i = 0; i < 10; i++)
            {
                Range<decimal> range = new Range<decimal>();
                range.FromValue = min + i*interval;
                range.ToValue = range.FromValue + interval;
                ranges.Add(range);
            }
            if (dbProviderType == DataProviderType.Oracle)
            {
                OracleConnection conn = new OracleConnection(connStr);
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    string selectSql1 = "select count (*) as Count from " + tableName;
                    for(int i=0;i<ranges.Count;i++)
                    {
                        if(i<ranges.Count -1)
                        {
                            string selectSql2 = "where " + fieldName + ">=" +
                                            FieldDataType.ToOracleDataValue(SqlDbType.Int,
                                                                            ranges[i].FromValue.ToString()) +
                                            " and " + fieldName + "<" +
                                            FieldDataType.ToOracleDataValue(SqlDbType.Int, ranges[i].ToValue.ToString());
                            cmd.CommandText = selectSql1 + " " + selectSql2;
                        }
                        else
                        {
                            string selectSql2 = "where " + fieldName + ">=" +
                                                FieldDataType.ToOracleDataValue(SqlDbType.Int,
                                                                                ranges[i].FromValue.ToString());
                            cmd.CommandText = selectSql1 + " " + selectSql2;
                        }
                        OracleDataReader reader = cmd.ExecuteReader();
                        while(reader.Read())
                        {
                            int count = 0;
                            try
                            {
                                count = int.Parse(reader[0].ToString());
                            }
                            catch
                            {
                                count = 0;
                            }
                            countByValues.Add(ranges[i], count);
                        }
                        reader.Close();
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                SqlConnection conn = new SqlConnection(connStr);
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    string selectSql1 = "select count (*) as Count from " + tableName;
                    for (int i = 0; i < ranges.Count; i++)
                    {
                        if (i < ranges.Count - 1)
                        {
                            string selectSql2 = "where " + fieldName + ">=" +
                                            FieldDataType.ToMssqlDataValue(SqlDbType.Int,
                                                                            ranges[i].FromValue.ToString()) +
                                            " and " + fieldName + "<" +
                                            FieldDataType.ToOracleDataValue(SqlDbType.Int, ranges[i].ToValue.ToString());
                            cmd.CommandText = selectSql1 + " " + selectSql2;
                        }
                        else
                        {
                            string selectSql2 = "where " + fieldName + ">=" +
                                                FieldDataType.ToMssqlDataValue(SqlDbType.Int,
                                                                                ranges[i].FromValue.ToString());
                            cmd.CommandText = selectSql1 + " " + selectSql2;
                        }
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            int count = 0;
                            try
                            {
                                count = int.Parse(reader[0].ToString());
                            }
                            catch
                            {
                                count = 0;
                            }
                            countByValues.Add(ranges[i], count);
                        }
                        reader.Close();
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return countByValues;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static Dictionary<Range<DateTime>, int> GetTableFieldCountByTimeRange(DataProviderType dbProviderType, string connStr, string schemaName, string tableName, string fieldName)
        {
            Dictionary<Range<DateTime>, int> countByValues = new Dictionary<Range<DateTime>, int>();
            DateTime min =
                DateTime.Parse(DbUtil.GetTableFieldMinValue(dbProviderType, connStr, schemaName, tableName, fieldName));
            DateTime max =
                DateTime.Parse(DbUtil.GetTableFieldMaxValue(dbProviderType, connStr, schemaName, tableName, fieldName));
            List<Range<DateTime>> ranges = new List<Range<DateTime>>();
            TimeSpan interval = new TimeSpan((max.Ticks - min.Ticks)/10);
            for (int i = 0; i < 10; i++)
            {
                Range<DateTime> range = new Range<DateTime>();
                range.FromValue = min.Add(new TimeSpan(interval.Ticks*i));
                range.ToValue = range.FromValue.Add(interval);
                ranges.Add(range);
            }
            if (dbProviderType == DataProviderType.Oracle)
            {
                OracleConnection conn = new OracleConnection(connStr);
                try
                {
                    conn.Open();
                    OracleCommand cmd = conn.CreateCommand();
                    string selectSql1 = "select count (*) as Count from " + tableName;
                    for (int i = 0; i < ranges.Count; i++)
                    {
                        if (i < ranges.Count - 1)
                        {
                            string selectSql2 = "where " + fieldName + ">=" +
                                            FieldDataType.ToOracleDataValue(SqlDbType.DateTime,
                                                                            ranges[i].FromValue.ToString()) +
                                            " and " + fieldName + "<" +
                                            FieldDataType.ToOracleDataValue(SqlDbType.DateTime, ranges[i].ToValue.ToString());
                            cmd.CommandText = selectSql1 + " " + selectSql2;
                        }
                        else
                        {
                            string selectSql2 = "where " + fieldName + ">=" +
                                                FieldDataType.ToOracleDataValue(SqlDbType.DateTime,
                                                                                ranges[i].FromValue.ToString());
                            cmd.CommandText = selectSql1 + " " + selectSql2;
                        }
                        OracleDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            int count = 0;
                            try
                            {
                                count = int.Parse(reader[0].ToString());
                            }
                            catch
                            {
                                count = 0;
                            }
                            countByValues.Add(ranges[i], count);
                        }
                        reader.Close();
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                SqlConnection conn = new SqlConnection(connStr);
                try
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    string selectSql1 = "select count (*) as Count from " + tableName;
                    for (int i = 0; i < ranges.Count; i++)
                    {
                        if (i < ranges.Count - 1)
                        {
                            string selectSql2 = "where " + fieldName + ">=" +
                                            FieldDataType.ToMssqlDataValue(SqlDbType.DateTime,
                                                                            ranges[i].FromValue.ToString()) +
                                            " and " + fieldName + "<" +
                                            FieldDataType.ToMssqlDataValue(SqlDbType.DateTime, ranges[i].ToValue.ToString());
                            cmd.CommandText = selectSql1 + " " + selectSql2;
                        }
                        else
                        {
                            string selectSql2 = "where " + fieldName + ">=" +
                                                FieldDataType.ToMssqlDataValue(SqlDbType.DateTime,
                                                                                ranges[i].FromValue.ToString());
                            cmd.CommandText = selectSql1 + " " + selectSql2;
                        }
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            int count = 0;
                            try
                            {
                                count = int.Parse(reader[0].ToString());
                            }
                            catch
                            {
                                count = 0;
                            }
                            countByValues.Add(ranges[i], count);
                        }
                        reader.Close();
                    }
                }
                catch (Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return countByValues;
        }
        #endregion

        #region drops
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static bool DropDatabase(string connStr, string dbName)
        {
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static bool DropTable(string connStr, string tblName)
        {
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="viewName"></param>
        /// <returns></returns>
        public static bool DropView(string connStr, string viewName)
        {
            return true;
        }
        #endregion

        #region new obj
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static bool NewDatabase(string connStr, string dbName)
        {
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="cols"></param>
        /// <returns></returns>
        public static bool NewTable(DataProviderType dbProvider, 
            string connStr, string schemaName, string tblName, 
            TableColumn[] cols)
        {
            string tgtTableName = tblName;
            

            if (dbProvider == DataProviderType.Oracle)
            {
                TableHelper.CreateOracleTable(connStr, tgtTableName, cols);
                return true;
            }
            else
            {
                if (!string.IsNullOrEmpty(schemaName))
                {
                    TableHelper.CreateTable(connStr, schemaName, tgtTableName, cols, true);
                }
                else
                {
                    TableHelper.CreateTable(connStr, tgtTableName, cols, true);
                }
                return true;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="viewName"></param>
        /// <param name="createSQL"></param>
        /// <returns></returns>
        public static bool NewView(string connStr, string viewName, string createSQL)
        {
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="pkFieldName"></param>
        /// <returns></returns>
        public static bool AddTablePrimaryKey(string connStr, string tblName, string pkFieldName)
        {
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="fkFieldName"></param>
        /// <param name="refersTblName"></param>
        /// <param name="refersFieldName"></param>
        /// <returns></returns>
        public static bool AddTableForeignKey(string connStr, string tblName, string fkFieldName, string refersTblName, string refersFieldName)
        {
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="fieldName"></param>
        /// <param name="constraintName"></param>
        /// <param name="constraintType"></param>
        /// <returns></returns>
        public static bool AddTableConstraint(string connStr, string tblName, string fieldName, string constraintName, string constraintType)
        {
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="spName"></param>
        /// <param name="spScript"></param>
        /// <returns></returns>
        public static bool NewStoredProc(string connStr, string spName, string spScript)
        {
            SqlConnection conn=new SqlConnection(connStr);
            bool success = false;
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string dropSql =
                    @"
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'P', N'PC'))
DROP PROCEDURE {0}";
                cmd.CommandText = string.Format(dropSql, spName);
                cmd.ExecuteNonQuery();
                cmd.CommandText = spScript;
                cmd.ExecuteNonQuery();
                success = true;
            }
            catch (Exception ex)
            {
                success = false;
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return success;
        }
        #endregion

        #region rename
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        public static bool RenameTable(string connStr, string tblName, string newName)
        {
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="viewName"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        public static bool RenameView(string connStr, string viewName, string newName)
        {
            return true;
        }
        #endregion

        #region Full-text search utilities
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static bool IsDatabaseFullTextEnabled(string connStr)
        {
            bool enabled = false;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string dbName = DbConnUtil.GetDatabaseName(connStr);
                cmd.CommandText = "select DATABASEPROPERTYEX ('" + dbName + "','IsFulltextEnabled')";
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    int result = int.Parse(reader[0].ToString());
                    if (result == 1)
                    {
                        enabled = true;
                    }
                    else
                    {
                        enabled = false;
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return enabled;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="enable"></param>
        public static void SetFullTextSearchCapabilityOnDatabase(string connStr, bool enable)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_fulltext_database";
                cmd.CommandType = CommandType.StoredProcedure;
                string actionValue = "enable";
                if (!enable)
                {
                    actionValue = "disable";
                }
                SqlParameter actionParam = new SqlParameter("@action", actionValue);
                cmd.Parameters.Add(actionParam);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <returns></returns>
        public static string[] GetFullTextCatalogs(string connStr)
        {
            List<string> catalogNames = new List<string>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select Name as FullTextCatalogName from sys.fulltext_catalogs";
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    catalogNames.Add(reader.GetString(0));
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            if (catalogNames.Count > 0)
            {
                return catalogNames.ToArray();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="catalogName"></param>
        /// <returns></returns>
        public static bool IsFullTextCatalogExists(string connStr, string catalogName)
        {
            string[] existingCatalogs = DbUtil.GetFullTextCatalogs(connStr);
            bool exist = false;
            if (existingCatalogs != null && existingCatalogs.Length > 0)
            {
                foreach (string catalog in existingCatalogs)
                {
                    if (catalog.ToLower() == catalogName.ToLower())
                    {
                        exist = true;
                        break;
                    }
                }
            }
            return exist;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static bool IsTableFullTextIndexed(string connStr, string tblName)
        {
            bool ftEnabled = false;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select sys.sysobjects.name from " + 
		            "sys.sysobjects where id in ("+
		            "select object_id from sys.fulltext_index_catalog_usages)"; 
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    string ftTableName = reader[0].ToString();
                    if (ftTableName.ToLower() == tblName.ToLower())
                    {
                        ftEnabled = true;
                        break;
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return ftEnabled;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="catalogName"></param>
        public static void CreateFullTextCatalog(string connStr, string catalogName)
        {
            bool catalogAlreadyExist = DbUtil.IsFullTextCatalogExists(connStr, catalogName);
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "sp_fulltext_catalog";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter catNameParam;
                SqlParameter actionParam;
                if (catalogAlreadyExist)
                {
                    catNameParam = new SqlParameter("@ftcat", catalogName);
                    actionParam = new SqlParameter("@action", "drop");
                    cmd.Parameters.AddRange(new SqlParameter[] { catNameParam, actionParam });
                    cmd.ExecuteNonQuery();
                }
                cmd.Parameters.Clear();
                catNameParam = new SqlParameter("@ftcat", catalogName);
                actionParam = new SqlParameter("@action", "create");
                cmd.Parameters.AddRange(new SqlParameter[] { catNameParam, actionParam });
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="catalogName"></param>
        /// <param name="tblName"></param>
        /// <param name="pkField"></param>
        /// <param name="txtColNames"></param>
        public static void CreateFullTextCatalogIndex(string connStr, string catalogName, string tblName, string pkField, string[] txtColNames)
        {
            bool isFtEnabled = DbUtil.IsTableFullTextIndexed(connStr, tblName);
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                // ft table
                cmd.CommandText = "sp_fulltext_table";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter tblParam = new SqlParameter("@tabname", tblName);
                SqlParameter actionParam = new SqlParameter("@action", "create");
                SqlParameter catNameParam = new SqlParameter("@ftcat", catalogName);
                SqlParameter keyParam = new SqlParameter("@keyname", pkField);
                if (isFtEnabled)
                {
                    actionParam = new SqlParameter("@action", "drop");
                    cmd.Parameters.AddRange(new SqlParameter[] { tblParam, actionParam });
                    cmd.ExecuteNonQuery();
                }
                cmd.Parameters.Clear();
                actionParam = new SqlParameter("@action", "create");
                cmd.Parameters.AddRange(new SqlParameter[] { tblParam, actionParam, catNameParam, keyParam });
                cmd.ExecuteNonQuery();

                // ft columns
                if (txtColNames != null && txtColNames.Length > 0)
                {
                    foreach (string colName in txtColNames)
                    {
                        cmd.CommandText = "sp_fulltext_column";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Clear();
                        actionParam = new SqlParameter("@action", "add");
                        SqlParameter colParam = new SqlParameter("@colname", colName);
                        cmd.Parameters.AddRange(new SqlParameter[] { tblParam, colParam, actionParam });
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        public static void StartFullTextIndex(string connStr, string tblName)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                // ft table
                cmd.CommandText = "sp_fulltext_table";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter tblParam = new SqlParameter("@tabname", tblName);
                SqlParameter actionParam = new SqlParameter("@action", "activate");
                cmd.Parameters.AddRange(new SqlParameter[] { tblParam, actionParam });
                cmd.ExecuteNonQuery();

                cmd.CommandText = "sp_fulltext_table";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                SqlParameter catlogParam = new SqlParameter("@tabname", tblName);
                actionParam = new SqlParameter("@action", "start_full");
                cmd.Parameters.AddRange(new SqlParameter[] { catlogParam, actionParam });
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

		/// <summary>
		/// 
		/// </summary>
		/// <param name="connStr"></param>
		/// <param name="tblName"></param>
		/// <returns></returns>
		public static List<string> GetFullTextIndexedColumnNames(string connStr, string tblName)
		{
			List<string> colNames = new List<string>();
			SqlConnection conn=new SqlConnection(connStr);
			try
			{
				conn.Open();
				SqlCommand cmd = conn.CreateCommand();
				cmd.CommandType = CommandType.Text;
				string sql =
				@"select c.name as ColumnName from sys.all_columns c where c.object_id=object_id('{0}') and column_id in (
select a.column_id from sys.fulltext_index_columns a, sys.sysobjects b
where a.object_id=b.id and b.name='{0}')";
				sql = string.Format(sql, tblName);
				cmd.CommandText = sql;
				SqlDataReader reader = cmd.ExecuteReader();
				while(reader.Read())
				{
					colNames.Add(reader[0].ToString());
				}
				reader.Close();
			}
			catch(Exception ex)
			{
				ExceptionHandler.HandlesException(ex);
			}
			finally
			{
				conn.Close();
			}
			return colNames;
			
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="connStr"></param>
		/// <param name="tableName"></param>
		/// <param name="keyField"></param>
		/// <param name="titleField">record label</param>
		/// <param name="textFieldName">full text indexed fields</param>
		/// <param name="query"></param>
		/// <param name="maxHits"></param>
		/// <returns></returns>
		public static List<FullTextSearchResult> DoFreeTextSearch(
			string connStr, string tableName, string keyField, string titleField,
            string textFieldName, string query, int maxHits)
		{
			List<FullTextSearchResult> hits=new List<FullTextSearchResult>();
            int N = DbUtil.GetTableRecordCount(DataProviderType.MSSQL, connStr, string.Empty, tableName);
			SqlConnection conn=new SqlConnection(connStr);
			try
			{
				conn.Open();
				SqlCommand cmd = conn.CreateCommand();
				cmd.CommandType = CommandType.Text;
				string sqlFreeTextTable = @"
SELECT 
	KEY_TBL.RANK, FT_TBL.{1}
FROM 
	{0} AS FT_TBL INNER JOIN 
	FREETEXTTABLE({0}, {2}, '{3}', {4}) AS KEY_TBL
ON 
	FT_TBL.{1} = KEY_TBL.[KEY]
ORDER BY 
	KEY_TBL.RANK DESC";
				cmd.CommandText = string.Format(sqlFreeTextTable, 
					tableName, keyField, textFieldName, query, maxHits);

                if(!string.IsNullOrEmpty(titleField))
                {
                    sqlFreeTextTable = @"
SELECT 
	KEY_TBL.RANK, FT_TBL.{1}, FT_TBL.{5} 
FROM 
	{0} AS FT_TBL INNER JOIN 
	FREETEXTTABLE({0}, {2}, '{3}', {4}) AS KEY_TBL
ON 
	FT_TBL.{1} = KEY_TBL.[KEY]
ORDER BY 
	KEY_TBL.RANK DESC";
                    cmd.CommandText = string.Format(sqlFreeTextTable,
                        tableName, keyField, textFieldName, query, maxHits, titleField);
                }

				SqlDataReader reader = cmd.ExecuteReader();
				while(reader.Read())
				{
					int rank = (int) reader["RANK"];
					int key = (int) reader[keyField];
				    string title = string.Empty;
                    if (!string.IsNullOrEmpty(titleField))
                        title = (string) reader[titleField];
				    hits.Add(new FullTextSearchResult(rank, key, 0, title));
				}
				reader.Close();

				if (hits.Count > 0)
				{
				    string[] terms = StringUtil.Split(query.ToLower(), " ");
                    List<string> uniqueTerms=new List<string>();
                    StopWordsUtil stopWordsUtil=new StopWordsUtil(StopWordsUtil.StopWordList.PubmedStopWordList);
				    List<string> stopWords = stopWordsUtil.StopWords;
                    for(int i=0;i<terms.Length;i++)
                    {
                        string term = terms[i];
                        term = StringUtil.RemoveNonAlphaNumericCharacters(term);
                        if(!string.IsNullOrEmpty(term) && !uniqueTerms.Contains(term) && !stopWords.Contains(term))
                        {
                            uniqueTerms.Add(term);
                        }
                    }
				    terms = uniqueTerms.ToArray();

					int avgFieldContentLen = 0;
					// key -> (term -> freq)
					Dictionary<int, Dictionary<string, int>> termFreqsByRecords = new Dictionary<int, Dictionary<string, int>>();
					// key -> field content len
					Dictionary<int, int> fieldContentLens = new Dictionary<int, int>();
					// term ->  doc count 
					Dictionary<string, int> docsContainingTerm = new Dictionary<string, int>();
					foreach (FullTextSearchResult hit in hits)
					{
						cmd.CommandText = string.Format("select {0} from {1} where {2}={3}", textFieldName, tableName, keyField, hit.Key);
						reader = cmd.ExecuteReader();
						string fieldContent = string.Empty;
						if (reader.Read() && reader[0] != null && reader[0] != DBNull.Value)
						{
							fieldContent = reader.GetString(0);
							fieldContent = fieldContent.ToLower();
						}
						reader.Close();
						avgFieldContentLen += (int)Math.Ceiling((double)fieldContent.Length / hits.Count);
						fieldContentLens.Add(hit.Key, fieldContent.Length);
						Dictionary<string, int> termFreqs = new Dictionary<string, int>();
						foreach (string term in terms)
						{
							int freq = 0;
							int pos = 0;
							pos = fieldContent.IndexOf(term, pos);
							while (pos >= 0)
							{
								freq++;
								pos = pos + term.Length;
								pos = fieldContent.IndexOf(term, pos);
							}
						    termFreqs.Add(term, freq);
							if (freq > 0)
							{
								if (docsContainingTerm.ContainsKey(term))
								{
									docsContainingTerm[term] = docsContainingTerm[term] + 1;
								}
								else
								{
									docsContainingTerm.Add(term, 1);
								}
							}
						}
						termFreqsByRecords.Add(hit.Key, termFreqs);
					}

					for (int i = 0; i < hits.Count; i++)
					{
						FullTextSearchResult hit = hits[i];
						double score = 0;
						foreach (string term in terms)
						{
							double n = 0;
							if (docsContainingTerm.ContainsKey(term))
								n = (double)docsContainingTerm[term];
							double idf = Math.Log((N - n + 0.5) / (n + 0.5));
							double f = 0;
							if (termFreqsByRecords[hit.Key].ContainsKey(term))
								f = (double)termFreqsByRecords[hit.Key][term];
							double D = fieldContentLens[hit.Key];
							double scale = f * (2.0 + 1) / (f + 2.0 * (1 - 0.75 + 0.75 * (D / avgFieldContentLen)));
							score += idf * scale;
						}
						hit.Score = score;
					}
				}
			}
			catch(Exception ex)
			{
				ExceptionHandler.HandlesException(ex);
			}
			finally
			{
				conn.Close();
			}

			return hits;
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tableName"></param>
        /// <param name="keyField"></param>
        /// <param name="titleField"></param>
        /// <param name="textFieldNames"></param>
        /// <param name="query"></param>
        /// <param name="maxHits"></param>
        /// <returns></returns>
        public static List<FullTextSearchResult> DoFreeTextSearch(
            string connStr, string tableName, string keyField, string titleField,
            string[] textFieldNames, string query, int maxHits)
        {
            List<FullTextSearchResult> hits = new List<FullTextSearchResult>();
            int N = DbUtil.GetTableRecordCount(DataProviderType.MSSQL, connStr, string.Empty, tableName);
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sqlFreeTextTable = @"
SELECT 
	KEY_TBL.RANK, FT_TBL.{1}
FROM 
	{0} AS FT_TBL INNER JOIN 
	FREETEXTTABLE({0}, ({2}), '{3}', {4}) AS KEY_TBL
ON 
	FT_TBL.{1} = KEY_TBL.[KEY]
ORDER BY 
	KEY_TBL.RANK DESC";
                cmd.CommandText = string.Format(sqlFreeTextTable,
                    tableName, keyField, StringUtil.ToString(textFieldNames), 
                    query, maxHits);

                if (!string.IsNullOrEmpty(titleField))
                {
                    sqlFreeTextTable = @"
SELECT 
	KEY_TBL.RANK, FT_TBL.{1}, FT_TBL.{5} 
FROM 
	{0} AS FT_TBL INNER JOIN 
	FREETEXTTABLE({0}, ({2}), '{3}', {4}) AS KEY_TBL
ON 
	FT_TBL.{1} = KEY_TBL.[KEY]
ORDER BY 
	KEY_TBL.RANK DESC";
                    cmd.CommandText = string.Format(sqlFreeTextTable,
                        tableName, keyField, StringUtil.ToString(textFieldNames), 
                        query, maxHits, titleField);
                }

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    int rank = (int)reader["RANK"];
                    int key = (int)reader[keyField];
                    string title = string.Empty;
                    if (!string.IsNullOrEmpty(titleField))
                        title = (string)reader[titleField];
                    hits.Add(new FullTextSearchResult(rank, key, 0, title));
                }
                reader.Close();

                if (hits.Count > 0)
                {
                    string[] terms = StringUtil.Split(query.ToLower(), " ");
                    List<string> uniqueTerms = new List<string>();
                    StopWordsUtil stopWordsUtil = new StopWordsUtil(StopWordsUtil.StopWordList.PubmedStopWordList);
                    List<string> stopWords = stopWordsUtil.StopWords;
                    for (int i = 0; i < terms.Length; i++)
                    {
                        string term = terms[i];
                        term = StringUtil.RemoveNonAlphaNumericCharacters(term);
                        if (!string.IsNullOrEmpty(term) && !uniqueTerms.Contains(term) && !stopWords.Contains(term))
                        {
                            uniqueTerms.Add(term);
                        }
                    }
                    terms = uniqueTerms.ToArray();

                    int avgFieldContentLen = 0;
                    // key -> (term -> freq)
                    Dictionary<int, Dictionary<string, int>> termFreqsByRecords = new Dictionary<int, Dictionary<string, int>>();
                    // key -> field content len
                    Dictionary<int, int> fieldContentLens = new Dictionary<int, int>();
                    // term ->  doc count 
                    Dictionary<string, int> docsContainingTerm = new Dictionary<string, int>();
                    foreach (FullTextSearchResult hit in hits)
                    {
                        cmd.CommandText = string.Format("select {0} from {1} where {2}={3}",
                                                        StringUtil.ToString(textFieldNames), tableName, keyField,
                                                        hit.Key);
                        reader = cmd.ExecuteReader();
                        string fieldContent = string.Empty;
                        if (reader.Read())
                        {
                            StringBuilder buffer=new StringBuilder();
                            foreach(string txtFieldName in textFieldNames)
                            {
                                if(reader[txtFieldName]!=null && reader[txtFieldName]!=DBNull.Value)
                                {
                                    buffer.AppendLine(reader[txtFieldName].ToString());
                                }
                            }
                            fieldContent = buffer.ToString().ToLower();
                        }
                        reader.Close();
                        avgFieldContentLen += (int)Math.Ceiling((double)fieldContent.Length / hits.Count);
                        fieldContentLens.Add(hit.Key, fieldContent.Length);
                        Dictionary<string, int> termFreqs = new Dictionary<string, int>();
                        foreach (string term in terms)
                        {
                            int freq = 0;
                            int pos = 0;
                            pos = fieldContent.IndexOf(term, pos);
                            while (pos >= 0)
                            {
                                freq++;
                                pos = pos + term.Length;
                                pos = fieldContent.IndexOf(term, pos);
                            }
                            termFreqs.Add(term, freq);
                            if (freq > 0)
                            {
                                if (docsContainingTerm.ContainsKey(term))
                                {
                                    docsContainingTerm[term] = docsContainingTerm[term] + 1;
                                }
                                else
                                {
                                    docsContainingTerm.Add(term, 1);
                                }
                            }
                        }
                        termFreqsByRecords.Add(hit.Key, termFreqs);
                    }

                    for (int i = 0; i < hits.Count; i++)
                    {
                        FullTextSearchResult hit = hits[i];
                        double score = 0;
                        foreach (string term in terms)
                        {
                            double n = 0;
                            if (docsContainingTerm.ContainsKey(term))
                                n = (double)docsContainingTerm[term];
                            double idf = Math.Log((N - n + 0.5) / (n + 0.5));
                            double f = 0;
                            if (termFreqsByRecords[hit.Key].ContainsKey(term))
                                f = (double)termFreqsByRecords[hit.Key][term];
                            double D = fieldContentLens[hit.Key];
                            double scale = f * (2.0 + 1) / (f + 2.0 * (1 - 0.75 + 0.75 * (D / avgFieldContentLen)));
                            score += idf * scale;
                        }
                        hit.Score = score;
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return hits;
        }

		/// <summary>
		/// 
		/// </summary>
		/// <param name="connStr"></param>
		/// <param name="tableName"></param>
		/// <param name="keyField"></param>
		/// <param name="titleField">label for record</param>
		/// <param name="textFieldName">full text indexed field</param>
		/// <param name="query"></param>
		/// <param name="exactMatch"></param>
		/// <param name="maxHits"></param>
		/// <returns></returns>
		public static List<FullTextSearchResult> DoContainsSearch(
			string connStr, string tableName, string keyField, string titleField,
            string textFieldName, string query, bool exactMatch, int maxHits)
		{
			List<FullTextSearchResult> hits = new List<FullTextSearchResult>();
			SqlConnection conn = new SqlConnection(connStr);
			try
			{
                if (exactMatch)
                    query = "\"" + query + "\"";
				conn.Open();
				SqlCommand cmd = conn.CreateCommand();
				cmd.CommandType = CommandType.Text;

				string sqlContainsTable = @"
SELECT 
   KEY_TBL.RANK,
   FT_TBL.{1} 
FROM 
   {0} AS FT_TBL INNER JOIN
   CONTAINSTABLE ({0}, {2}, '({3})', {4}) AS KEY_TBL
ON 
   FT_TBL.{1} = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC";
				cmd.CommandText = string.Format(sqlContainsTable,
					tableName, keyField, textFieldName, query, maxHits);

                if(!string.IsNullOrEmpty(titleField))
                {
                    sqlContainsTable = @"
SELECT 
   KEY_TBL.RANK,
   FT_TBL.{1},
   FT_TBL.{2}, 
FROM 
   {0} AS FT_TBL INNER JOIN
   CONTAINSTABLE ({0}, {3}, '({4})', {5}) AS KEY_TBL
ON 
   FT_TBL.{1} = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC";
                    cmd.CommandText = string.Format(sqlContainsTable,
                        tableName, keyField, titleField, textFieldName, query, maxHits);
                }

				SqlDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					int rank = (int)reader["RANK"];
					int key = (int)reader[keyField];
				    string title = string.Empty;
                    if (!string.IsNullOrEmpty(titleField))
                        title = (string) reader[titleField];
					hits.Add(new FullTextSearchResult(rank, key, 0, title));
				}
				reader.Close();

				if (hits.Count > 0)
				{
                    string[] terms = StringUtil.Split(query.ToLower(), " ");
                    List<string> uniqueTerms = new List<string>();
                    StopWordsUtil stopWordsUtil = new StopWordsUtil(StopWordsUtil.StopWordList.PubmedStopWordList);
                    List<string> stopWords = stopWordsUtil.StopWords;
                    for (int i = 0; i < terms.Length; i++)
                    {
                        string term = terms[i];
                        term = StringUtil.RemoveNonAlphaNumericCharacters(term);
                        if (!string.IsNullOrEmpty(term) && !uniqueTerms.Contains(term) && !stopWords.Contains(term))
                        {
                            uniqueTerms.Add(term);
                        }
                    }
                    terms = uniqueTerms.ToArray();

					int avgFieldContentLen = 0;
					// key -> (term -> freq)
					Dictionary<int,Dictionary<string,int>> termFreqsByRecords=new Dictionary<int, Dictionary<string, int>>();
					// key -> field content len
					Dictionary<int, int> fieldContentLens = new Dictionary<int, int>(); 
					// term ->  doc count 
					Dictionary<string,int> docsContainingTerm=new Dictionary<string, int>();
					foreach(FullTextSearchResult hit in hits)
					{
						cmd.CommandText = string.Format("select {0} from {1} where {2}={3}", textFieldName, tableName, keyField, hit.Key);
						reader = cmd.ExecuteReader();
						string fieldContent = string.Empty;
						if(reader.Read() && reader[0]!=null && reader[0]!=DBNull.Value)
						{
							fieldContent = reader.GetString(0);
							fieldContent = fieldContent.ToLower();
						}
						reader.Close();
						avgFieldContentLen += (int) Math.Ceiling((double) fieldContent.Length/hits.Count);
						fieldContentLens.Add(hit.Key,fieldContent.Length);
						Dictionary<string,int> termFreqs=new Dictionary<string, int>();
						foreach(string term in terms)
						{
							int freq = 0;
							int pos = 0;
							pos = fieldContent.IndexOf(term, pos);
							while(pos>=0)
							{
								freq++;
								pos = pos + term.Length;
								pos = fieldContent.IndexOf(term, pos);
							}
							termFreqs.Add(term,freq);

							if(freq>0)
							{
								if(docsContainingTerm.ContainsKey(term))
								{
									docsContainingTerm[term] = docsContainingTerm[term] + 1;
								}
								else
								{
									docsContainingTerm.Add(term, 1);
								}
							}
						}
						termFreqsByRecords.Add(hit.Key,termFreqs);
					}

					for(int i=0;i<hits.Count;i++)
					{
						FullTextSearchResult hit = hits[i];
						double N = (double) hits.Count;
						double score = 0;
						foreach(string term in terms)
						{
							double n = 0;
							if (docsContainingTerm.ContainsKey(term))
								n = (double) docsContainingTerm[term];
							double idf = Math.Log((N - n + 0.5) / (n + 0.5));
							double f = 0;
							if (termFreqsByRecords[hit.Key].ContainsKey(term))
								f = (double) termFreqsByRecords[hit.Key][term];
							double D = fieldContentLens[hit.Key];
							double scale = f*(2.0 + 1)/(f + 2.0*(1 - 0.75 + 0.75*(D/avgFieldContentLen)));
							score += idf*scale;
						}
						hit.Score = score;
					}
				}
			}
			catch (Exception ex)
			{
				ExceptionHandler.HandlesException(ex);
			}
			finally
			{
				conn.Close();
			}

			return hits;
		}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="keyField"></param>
        /// <param name="keyValue"></param>
        /// <param name="txtFields"></param>
        /// <param name="maxNeighbors"></param>
        /// <returns></returns>
        public static List<FullTextSearchResult> GetTextNeighbors(
            string connStr, string tblName, string keyField, int keyValue,
            List<string> txtFields, int maxNeighbors)
        {
            string spName = "QuoteString";
            if (!DbUtil.IsStoredProcedureExist(connStr, spName))
            {
                string spBody = Common.Properties.Resources.QuoteString;
                DbUtil.NewStoredProc(connStr, spName, spBody);
            }
            spName = "FindTextNeighbors";
            if(!DbUtil.IsStoredProcedureExist(connStr, spName))
            {
                string spBody = Properties.Resources.FindTextNeighbors;
                DbUtil.NewStoredProc(connStr, spName, spBody);
            }
            List<FullTextSearchResult> hits=new List<FullTextSearchResult>();
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "FindTextNeighbors";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter ftblParam = new SqlParameter("@FtTableName",tblName);
                SqlParameter keyFieldParam = new SqlParameter("@KeyName",keyField);
                string txtFieldVal = string.Empty;
                foreach(string txtField in txtFields)
                {
                    if (txtFieldVal.Length > 0)
                        txtFieldVal += ",";
                    txtFieldVal += "[" + txtField + "]";
                }
                SqlParameter txtFieldsParam = new SqlParameter("@TxtFields", txtFieldVal);
                SqlParameter keyParam=new SqlParameter("@Key",keyValue);
                cmd.Parameters.AddRange(new SqlParameter[]{ftblParam,keyFieldParam,txtFieldsParam,keyParam});
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    int rank = int.Parse(reader["Rank"].ToString());
                    int key = int.Parse(reader[keyField].ToString());
                    FullTextSearchResult hit=new FullTextSearchResult(rank,key,0,"");
                    hits.Add(hit);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return hits;
        }
        #endregion

        #region retrieval
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="keyFieldName"></param>
        /// <param name="fieldValues"></param>
        /// <returns></returns>
        public static DataTable GrabDataByKeyValues(string connStr, string tblName, string keyFieldName, List<int> fieldValues)
        {
            string tmpTblName = "tmpTable_" + DateTime.Now.Ticks.ToString();
            TableColumn keyCol = new TableColumn();
            keyCol.AllowNull = false;
            keyCol.ColumnName = keyFieldName;
            keyCol.DbType = SqlDbType.Int;
            keyCol.Size = FieldDataType.GetFieldDefaultSize(SqlDbType.Int);
            TableHelper.CreateTable(connStr, tmpTblName, new TableColumn[] { keyCol }, true);
            SqlConnection conn = new SqlConnection(connStr);
            DataTable dtOutput = null;
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                foreach (int keyVal in fieldValues)
                {
                    cmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tmpTblName, keyFieldName, keyVal);
                    cmd.ExecuteNonQuery();
                }
                DbUtil.AddIndex(connStr, tmpTblName, keyFieldName, "idx_" + tmpTblName + "_" + keyFieldName);

                cmd.CommandText = string.Format("select a.* from {0} a, {1} b where a.{2}=b.{2}", tblName, tmpTblName, keyFieldName);
                SqlDataAdapter daSelect = new SqlDataAdapter(cmd);
                DataSet dsSelect = new DataSet();
                daSelect.Fill(dsSelect);
                dtOutput = dsSelect.Tables[0];
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            TableHelper.DropTable(connStr, tmpTblName);

            return dtOutput;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="sql"></param>
        /// <param name="keyFieldName"></param>
        /// <param name="fieldValues"></param>
        /// <returns></returns>
        public static DataTable GrabDataBySqlAndKeyValues(string connStr, string sql, string keyFieldName, List<int> fieldValues)
        {
            string tmpTblName = "tmpTable_" + DateTime.Now.Ticks.ToString();
            TableColumn keyCol = new TableColumn();
            keyCol.AllowNull = false;
            keyCol.ColumnName = keyFieldName;
            keyCol.DbType = SqlDbType.Int;
            keyCol.Size = FieldDataType.GetFieldDefaultSize(SqlDbType.Int);
            TableHelper.CreateTable(connStr, tmpTblName, new TableColumn[] { keyCol }, true);
            SqlConnection conn = new SqlConnection(connStr);
            DataTable dtOutput = null;
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                foreach (int keyVal in fieldValues)
                {
                    cmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tmpTblName, keyFieldName, keyVal);
                    cmd.ExecuteNonQuery();
                }
                DbUtil.AddIndex(connStr, tmpTblName, keyFieldName, "idx_" + tmpTblName + "_" + keyFieldName);

                cmd.CommandText = string.Format("select a.* from ({0}) a, {1} b where a.{2}=b.{2}", 
                    sql, tmpTblName, keyFieldName);
                SqlDataAdapter daSelect = new SqlDataAdapter(cmd);
                DataSet dsSelect = new DataSet();
                daSelect.Fill(dsSelect);
                dtOutput = dsSelect.Tables[0];
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            TableHelper.DropTable(connStr, tmpTblName);

            return dtOutput;
        }
        #endregion

        #region temp tables
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tmpTableName"></param>
        /// <param name="fieldName"></param>
        /// <param name="keyValues"></param>
        public static void NewTempKeyTable(
            string connStr, string tmpTableName, 
            string fieldName, List<int> keyValues)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
                    string.Format("create table {0} ({1} int)", tmpTableName, fieldName);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                DataTable dtSrc = new DataTable(tmpTableName);
                dtSrc.Columns.Add(fieldName, typeof (int));

                foreach(int key in keyValues)
                {
                    DataRow dr = dtSrc.NewRow();
                    dr[0] = key;
                    dtSrc.Rows.Add(dr);
                    
                }
                using (SqlBulkCopy copy = new SqlBulkCopy(connStr))
                {
                    copy.DestinationTableName = tmpTableName;
                    copy.BulkCopyTimeout = 60*60;   // 1 hr
                    copy.WriteToServer(dtSrc);
                }
                
                cmd.CommandText = string.Format("create index idx_{0}_{1} on {0} ({1})", tmpTableName, fieldName);
                cmd.CommandTimeout = 15*60; // 15 min 
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="connStr"></param>
        /// <param name="tmpTableName"></param>
        public static void DropTempTable(DataProviderType provider, string connStr, string tmpTableName)
        {
            IDbConnection conn = null;
            if (provider == DataProviderType.MSSQL)
                conn = new SqlConnection(connStr);
            else
                conn = new OracleConnection(connStr);

            try
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText =
                    string.Format("drop table {0}", tmpTableName);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region Access rights
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <returns></returns>
        public static bool CanQuery(DataProviderType dbProvider, string connStr, string schemaName)
        {
            string[] tbls = DbUtil.GetTableNames(dbProvider, schemaName, connStr);
            if(tbls==null || tbls.Length==0)
                return false;
            foreach(string tblName in tbls)
            {
                try
                {
                    int recCount = DbUtil.GetTableRecordCount(dbProvider, connStr, schemaName, tblName);
                    if(recCount>0)
                        return true;
                }
                catch(Exception)
                {
                    return false;
                }
            }
            return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <returns></returns>
        public static bool CanQueryTable(DataProviderType dbProvider, string connStr, string schemaName, string tblName)
        {
            try
            {
                int recCount = DbUtil.GetTableRecordCount(dbProvider, connStr, schemaName, tblName);
                if (recCount > 0)
                    return true;
            }
            catch (Exception)
            {
                return false;
            }
            return false;
        }
        #endregion

        ///<summary>
        ///</summary>
        ///<param name="connStr"></param>
        ///<param name="sql"></param>
        public static void ExecuteSqlCommand(string connStr, string sql)
        {
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        ///<summary>
        ///</summary>
        ///<param name="dbProvider"></param>
        ///<param name="connStr"></param>
        ///<param name="schemaName"></param>
        ///<param name="tblName"></param>
        ///<param name="colName"></param>
        ///<returns></returns>
        public static bool ContainsNullValue(DataProviderType dbProvider, string connStr, string schemaName, string tblName, string colName)
        {
            if(dbProvider==DataProviderType.Oracle)
            {
                string sql = string.Format(
                    "select count (*) from {0}.[{1}] T1 where T1.{2} is null", schemaName, tblName, colName);
                OracleConnection conn1=new OracleConnection(connStr);
                try
                {
                    conn1.Open();
                    IDbCommand cmd = conn1.CreateCommand();
                    cmd.CommandText = sql;
                    int count = int.Parse(cmd.ExecuteScalar().ToString());
                    if(count > 0)
                        return true;
                    else
                        return false;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    conn1.Close();
                }
            }
            else
            {
                string sql = string.Format(
                    "select count (*) from {0}.[{1}] T1 where T1.[{2}] is null", schemaName, tblName, colName);
                SqlConnection conn1 = new SqlConnection(connStr);
                try
                {
                    conn1.Open();
                    IDbCommand cmd = conn1.CreateCommand();
                    cmd.CommandText = sql;
                    int count = int.Parse(cmd.ExecuteScalar().ToString());
                    if (count > 0)
                        return true;
                    else
                        return false;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    conn1.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="dbName1"></param>
        /// <param name="schema1"></param>
        /// <param name="tblName1"></param>
        /// <param name="colName1"></param>
        /// <param name="dbName2"></param>
        /// <param name="schema2"></param>
        /// <param name="tblName2"></param>
        /// <param name="colName2"></param>
        /// <returns></returns>
        public static bool ContainsAllValues(
            DataProviderType dbProvider, string connStr, 
            string dbName1, string schema1, string tblName1, string colName1, 
            string dbName2, string schema2, string tblName2, string colName2)
        {
            string sql = @"
select count (*) 
from 
    {0}.{1}.[{2}] T1 where T1.[{3}] not in (
        select distinct T2.[{7}] from {4}.{5}.[{6}] T2)";
            if (dbProvider == DataProviderType.Oracle)
                sql = @"
select count (*) 
from 
    {0}.{1}.[{2}] T1 where T1.{3} not in (
        select distinct T2.{7} from {4}.{5}.[{6}] T2)";
            sql = string.Format(
                sql, dbName2, schema2, tblName2, colName2, 
                dbName1, schema1, tblName1, colName1);
            IDbConnection conn = null;
            if (dbProvider == DataProviderType.Oracle)
                conn = new OracleConnection(connStr);
            else
                conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                int count = int.Parse(cmd.ExecuteScalar().ToString());
                if(count>0)
                    return false;
                else
                    return true;
            }
            catch
            {
                return true;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static FieldStat GetTableFieldStat(
            DataProviderType dbProvider, 
            string connStr, string schemaName, string tblName, 
            string colName, SqlDbType dbType)
        {
            FieldStat fieldStat=new FieldStat();
            fieldStat.FieldName = colName;
            fieldStat.DataType = SimpleDataTypeUtil.ToSimpleDataType(dbType);

            IDbConnection conn = null;
            if (dbProvider == DataProviderType.Oracle)
                conn = new OracleConnection(connStr);
            else
                conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sql = string.Format(
                    "select count (*) from {0}.[{1}]",
                    schemaName, tblName);
                cmd.CommandText = sql;
                int count = 0;
                int missingCount = 0;
                int distValueCount = 0;
                cmd.CommandText = sql;
                cmd.CommandTimeout = 5 * 60; // 1 min
                int.TryParse(cmd.ExecuteScalar().ToString(), out count);
                sql = string.Format(
                    "select count (distinct {0}) from {1}.[{2}] where {0} is not null",
                    colName, schemaName, tblName);
                cmd.CommandText = sql;
                cmd.CommandTimeout = 10*60;
                int.TryParse(cmd.ExecuteScalar().ToString(), out distValueCount);
                sql = string.Format(
                    "select count (*) from {1}.[{2}] where {0} is null",
                    colName, schemaName, tblName);
                cmd.CommandText = sql;
                cmd.CommandTimeout = 5 * 60;
                int.TryParse(cmd.ExecuteScalar().ToString(), out missingCount);
                fieldStat.DistinctValueCount = distValueCount;
                fieldStat.Count = count;
                fieldStat.MissingValueCount = missingCount;
                string max = null;
                string min = null;
                string avg = null;
                string mode = null;
                string sd = null;

                if (FieldDataType.IsNumber(dbType))
                {
                    sql = string.Format(
                        "select max ({0}) from {1}.[{2}]",
                        colName, schemaName, tblName);
                    cmd.CommandText = sql;
                    cmd.CommandTimeout = 5 * 60;
                    max = cmd.ExecuteScalar().ToString();
                    sql = string.Format(
                        "select min ({0}) from {1}.[{2}]",
                        colName, schemaName, tblName);
                    cmd.CommandText = sql;
                    cmd.CommandTimeout = 5 * 60;
                    min = cmd.ExecuteScalar().ToString();
                    sql = string.Format(
                        "select avg (cast({0} as float)) from {1}.[{2}]",
                        colName, schemaName, tblName);
                    cmd.CommandText = sql;
                    cmd.CommandTimeout = 60*10;
                    avg = cmd.ExecuteScalar().ToString();
                    sql = string.Format("select STDEV ([{0}]) from {1}.[{2}]", colName, schemaName, tblName);
                    cmd.CommandText = sql;
                    cmd.CommandTimeout = 60*30;
                    sd = cmd.ExecuteScalar().ToString();
                }
                if (fieldStat.DistinctValueCount > 0)
                {
                    sql = string.Format(
                        "select top 1 ({0}) from {1}.[{2}] where {0} is not null group by {0} order by count (*) desc",
                        colName, schemaName, tblName);
                    cmd.CommandText = sql;
                    cmd.CommandTimeout = 60 * 10;
                    mode = cmd.ExecuteScalar().ToString();
                }
                fieldStat.MaxValue = SimpleDataTypeUtil.ToDbValue(fieldStat.DataType, max);
                fieldStat.MinValue = SimpleDataTypeUtil.ToDbValue(fieldStat.DataType, min);
                fieldStat.Average = SimpleDataTypeUtil.ToDbValue(fieldStat.DataType, avg);
                fieldStat.Mode = SimpleDataTypeUtil.ToDbValue(fieldStat.DataType, mode);
                fieldStat.SD = SimpleDataTypeUtil.ToDbValue(fieldStat.DataType, sd);
                fieldStat.Distributions = new Dictionary<object, int>();

                if((fieldStat.DistinctValueCount>0 && fieldStat.DistinctValueCount <= 20) || 
                    (fieldStat.Count / 100 >= fieldStat.DistinctValueCount &&
                    fieldStat.DistinctValueCount<=50))
                {
                    sql = string.Format(
                        "select {0}, count (*) as freq from {1}.[{2}] where {0} is not null group by {0}",
                        colName, schemaName, tblName);
                    cmd.CommandText = sql;
                    IDataReader reader = cmd.ExecuteReader();
                    while(reader.Read())
                    {
                        object value = SimpleDataTypeUtil.ToDbValue(fieldStat.DataType, reader[0].ToString());
                        int freq = int.Parse(reader["freq"].ToString());
                        fieldStat.Distributions.Add(value, freq);
                    }
                    reader.Close();
                }
                else if (FieldDataType.IsNumber(dbType) && fieldStat.MinValue !=null && 
                    fieldStat.MaxValue !=null) // 10 percentile
                {
                    double minimumNumber = double.Parse(fieldStat.MinValue.ToString());
                    double maximumNumber = double.Parse(fieldStat.MaxValue.ToString());
                    double interval = (maximumNumber - minimumNumber)/10;
                    double floor = minimumNumber;
                    double ceiling = floor + interval;
                    while (ceiling <=maximumNumber)
                    {
                        sql = string.Format(
                            "select count (*) as freq from {1}.[{2}] where [{0}] is not null and [{0}]>={3} and [{0}]<{4}",
                            colName, schemaName, tblName, floor, ceiling);
                        if(ceiling >=maximumNumber)
                        {
                            sql = string.Format(
                                "select count (*) as freq from {1}.[{2}] where [{0}] is not null and [{0}]>={3} and [{0}]<={4}",
                                colName, schemaName, tblName, floor, ceiling);
                        }
                        cmd.CommandText = sql;
                        IDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            Range<double> bin=new Range<double>(floor, ceiling);
                            int freq = int.Parse(reader["freq"].ToString());
                            fieldStat.Distributions.Add(bin, freq);
                        }
                        reader.Close();
                        floor = ceiling;
                        ceiling += interval;
                    }
                    
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return fieldStat;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(DataProviderType dbProvider,
            string connStr, string sql)
        {
            IDbConnection conn = null;
            if (dbProvider == DataProviderType.Oracle)
                conn = new OracleConnection(connStr);
            else
                conn = new SqlConnection(connStr);
            object result = null;
            try
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 60*10;
                result = cmd.ExecuteScalar();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        private static void ExecuteStoredProcedure(string connStr, string spName, Dictionary<string, object> spParameters, int timeoutSeconds)
        {
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = spName;
                cmd.CommandTimeout = timeoutSeconds;
                cmd.Parameters.Clear();
                if(spParameters !=null && spParameters.Count>0)
                {
                    foreach(string paramName in spParameters.Keys)
                    {
                        SqlParameter sqlParam=new SqlParameter("@"+paramName, spParameters[paramName]);
                        cmd.Parameters.Add(sqlParam);
                    }
                }
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataProviderType"></param>
        /// <param name="connStr"></param>
        /// <param name="schemaName"></param>
        /// <param name="tblName"></param>
        /// <param name="colName"></param>
        /// <param name="idxName"></param>
        public static void CreateIndex(DataProviderType dataProviderType, string connStr, 
            string schemaName, string tblName, string colName, string idxName)
        {
            string sql = string.Format("Create index {0} on {1}.[{2}] ([{3}])",
                                       idxName, schemaName, tblName, colName);
            if (dataProviderType == DataProviderType.Oracle)
                sql = string.Format(
                    "Create Or Replace index {0} on {1}.[{2}] ([{3}])",
                    idxName, schemaName, tblName, colName);
            IDbConnection conn = null;
            if (dataProviderType == DataProviderType.Oracle)
                conn = new OracleConnection(connStr);
            else
                conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;

                if (dataProviderType == DataProviderType.MSSQL)
                {
                    string checkSql =
                        @"
SELECT * FROM sys.indexes A WHERE 
	A.name='{0}' 
	AND A.object_id IN (
        SELECT OBJECT_ID FROM sys.all_objects WHERE name='{2}' AND SCHEMA_ID IN (SELECT SCHEMA_ID FROM sys.schemas WHERE name='{1}'))
	AND A.index_id IN (
		SELECT index_id FROM sys.index_columns B WHERE B.column_id IN (
			SELECT column_id FROM SYS.all_columns WHERE name='{3}' AND OBJECT_ID IN (
				SELECT OBJECT_ID FROM sys.all_objects WHERE name='{2}' AND SCHEMA_ID IN (
					SELECT SCHEMA_ID FROM sys.schemas WHERE name='{1}'
				)
			)
		)
	)";
                    checkSql = string.Format(checkSql, idxName, schemaName, tblName, colName);
                    cmd.CommandText = checkSql;
                    cmd.ExecuteNonQuery();
                }

                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
